What is the proper way to escape a $_SESSION username for use in a mysql query?

Question

I am using the stored $_SESSION username:

$usernameunesc = htmlentities($_SESSION['username'], ENT_QUOTES, 'UTF-8');

and escaping the variable as follows:

$username = mysqli_real_escape_string($link, $usernameunesc);

and then using it to perform the following query:

$query = mysqli_query($link, "SELECT id FROM users WHERE username = '".$username."'");

The last query returns an output of

If I remove $username from the query and change it to 'demo' (which is what the username actually is) the query returns an id of 12 successfully, but using the $username variable does not work. Am I not properly escaping the string? Is there a better way to do this? By the way, I can also perform the query in the SQL command line and it works fine, so I know the query isn't the problem.

yes there is, read this article: http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php — John Woo, May 03 '13 at 17:21
I think your quotes are wrong. Try making your query `"SELECT id FROM users WHERE username = '$username'"` — ntgCleaner, May 03 '13 at 17:22

score 1 · Answer 1 · answered May 03 '13 at 17:25

As you are using mysqli, I suggest you use a prepared statement - it will automatically escape the variable.

if ($stmt = $mysqli->prepare("SELECT id FROM users WHERE username =?")) {

    /* bind parameters for markers */
    $stmt->bind_param("s", $usernameunesc);

    /* execute query */
    $stmt->execute();
}

Full example right here http://php.net/manual/en/mysqli.prepare.php

What is the proper way to escape a $_SESSION username for use in a mysql query?

1 Answers1