10
\$\begingroup\$

I was wondering if my code is safe for sql injection. This code just checks if the username exists in my db or not.

$username = $_POST['username'];
$stmt = mysqli_stmt_init($con);
$query = "SELECT username FROM users WHERE username = ?" ;
mysqli_stmt_prepare($stmt, $query);
mysqli_stmt_bind_param($stmt, "s", $username);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $user['username']);
mysqli_stmt_execute($stmt);
if (mysqli_stmt_fetch($stmt)){
    if ($user['username'] === $username){
    echo $username, ' exists';
    }
}
elseif (!mysqli_stmt_fetch($stmt)){
    echo $username, ' doesn\'t exists';
}
\$\endgroup\$
  • \$\begingroup\$ Looks safe enough. If you're going to have lots of users at some point you may consider making a SQL stored procedure, but otherwise I don't think you will have any problems. \$\endgroup\$ – Phrancis Jul 15 '14 at 14:39
  • \$\begingroup\$ Have you tested this code to make sure that it produces the correct results? Parts of what you are doing here looks horribly wrong. \$\endgroup\$ – Simon Forsberg Jul 15 '14 at 14:46
  • \$\begingroup\$ @Simon André Forsberg Yes I tested this and everything works fine. \$\endgroup\$ – Thoaren Jul 15 '14 at 14:50
  • \$\begingroup\$ @Simon André Forsberg What part is it that looks wrong for you? \$\endgroup\$ – Thoaren Jul 15 '14 at 14:51
  • 2
    \$\begingroup\$ SQL injection safe, but mind XSS attack. Displaying raw user supplied code could execute script. Try to find this user: <script>alert("XSS!");</script> \$\endgroup\$ – shudder Jul 15 '14 at 16:59
10
\$\begingroup\$

SQL Injection-wise, this is completely safe. You don't run any risk of SQL Injection.

However, some parts of your code is not optimal:

mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $user['username']);
mysqli_stmt_execute($stmt);

Why are you executing the statement twice?


Now, imagine that you hade your if-else switched so that you wanted to check for non-existing user first:

if (!mysqli_stmt_fetch($stmt)) {
    echo $username, ' doesn\'t exists';
}
elseif (mysqli_stmt_fetch($stmt)) {
    if ($user['username'] === $username) {
        echo $username, ' exists';
    }
}

This code would not work, and you might not be aware of why exactly. You might be just lucky that you did not code it this way from the start.

The issue is that you are calling mysqli_stmt_fetch twice. You should not do that, there can only be a maximum of one result, which means that the second if will always be false.

Your original code should look like this:

if (mysqli_stmt_fetch($stmt)) {
    if ($user['username'] === $username){
        echo $username, ' exists';
    }
}
else {
    echo $username, ' doesn\'t exists';
}

In fact though, if the first if-statement is true, then the inner if will also be true because of your SQL WHERE condition. So your code could be just this:

if (mysqli_stmt_fetch($stmt)) {
    echo $username, ' exists';
}
else {
    echo $username, ' doesn\'t exists';
}
\$\endgroup\$
2
\$\begingroup\$

This is the full code to grab the username from a db using mysqli
Check mysqli_stmt_fetch($stmt) for a doesExist and echo appropriately or use simons example above if (mysqli_stmt_fetch($stmt)) {

$con = mysqli_connect("localhost","name","pass","database") or die("Error " . mysqli_error($con)); 
$username = $_POST['username'];
$stmt = mysqli_stmt_init($con);
mysqli_stmt_prepare($stmt,"SELECT username FROM table WHERE username = ?");
mysqli_stmt_bind_param($stmt, "s", $username);
mysqli_stmt_execute($stmt);
$doesExist = mysqli_stmt_fetch($stmt);
mysqli_stmt_close($stmt);
if($doesExist) {
    echo $username. ' exists';
}
else {
    echo $username. ' doesn\'t exist';
}

Callable:

$con = mysqli_connect("localhost","name","pass","database") or die("Error " . mysqli_error($con)); 

if(isset($_POST['username'])) {
    $username = $_POST['username'];
    $stmt = mysqli_stmt_init($con);
    mysqli_stmt_prepare($stmt,"SELECT username FROM table WHERE username = ?");
    mysqli_stmt_bind_param($stmt, "s", $username);
    mysqli_stmt_execute($stmt);
    $doesExist = mysqli_stmt_fetch($stmt);
    mysqli_stmt_close($stmt);
    if($doesExist) {
        echo $username. ' exists';
    }
    else {
        echo $username. ' doesn\'t exist';
    }
}

As a Function:

function checkUsername($username) {
    $con = mysqli_connect("localhost","name","pass","database") or die("Error " . mysqli_error($con)); 
    $stmt = mysqli_stmt_init($con);
    mysqli_stmt_prepare($stmt,"SELECT username FROM table WHERE username = ?");
    mysqli_stmt_bind_param($stmt, "s", $username);
    mysqli_stmt_execute($stmt);
    $doesExist = mysqli_stmt_fetch($stmt);
    mysqli_stmt_close($stmt);
    if($doesExist) {
        echo $username. ' exists';
    }
    else {
        echo $username. ' doesn\'t exist';
    }
}

Function Usage

if(isset($_POST['username'])) {
    checkUsername($_POST['username']);  
}

Optional Form

(This form requires the above function to be on the same page)

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
<input type="text" name="username"/>
<input type="submit" value="Check Username">

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.