SQL injection safety check

Question

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';
}

• Phrancis: 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.
• Simon Forsberg: Have you tested this code to make sure that it produces the correct results? Parts of what you are doing here looks horribly wrong.
• Thoaren: @Simon André Forsberg Yes I tested this and everything works fine.
• Thoaren: @Simon André Forsberg What part is it that looks wrong for you?
• shudder: SQL injection safe, but mind XSS attack. Displaying raw user supplied code could execute script. Try to find this user: <script>alert("XSS!");</script>

Answer 1

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';
}

Answer 2

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">