String value with an appostrophe in a PHP variable email address, mysql select

Question

I have an email address mike.o'[email protected] stored in a posted variable and I want a select statement in mysql to validate the existance of the email address and retrieve a password from my table. My query gets stuck at the apostophe when it trys to execute. eg "SELECT pwd FROM tbl_users WHERE userName = '$email'"; Any assistance will be much appreciated, thanks in advance.

Answer 1

You should use mysql_real_escape_string to quote the value. In fact, you should use it every time you insert a value in a query, if you dont, you are not only open to errors, also to SQL Injection.

You should use it like this:

if ( get_magic_quotes_gpc() ) {
    $email = stripslashes($email);
}
$quoted_email = mysql_real_escape_string($email, $db_connection);
$query = "SELECT pwd FROM tbl_users WHERE userName='".$quoted_email."'";

Edit: If PHP has magic quotes on, all superglobals values ( values in $_GET, $_POST, ... ) are quoted with addslashes which sucks. You should consider turning it off.

Answer 2

Use mysql_real_escape_string, for example:

$email = mysql_real_escape_string($email);

Note this should be done regardless when using variable strings in unprepared SQL to prevent SQL injection vulnerabilities.

Answer 3

How about wrapping $email in PHP's mysql_real_escape_string? Sanitizing all your user generated input is a good idea..

http://us.php.net/manual/en/function.mysql-real-escape-string.php

EDIT:

Mike "\" is the MySQL escape character. Here is the MySQL 5.0's documentation on strings.

mysql_real_escape_string correctly escapes the email address as 'mike.o\'[email protected]'. Here is a full example:

// Connect
$link = mysql_connect('localhost', 'simeon', 'password')
    OR die(mysql_error());

$db_selected = mysql_select_db('db', $link);

$email = "mike.o'[email protected]";
// Query
$query = "SELECT email FROM users WHERE email='".mysql_real_escape_string($email)."'";

$result = mysql_query($query);   

while ($row = mysql_fetch_assoc($result)) {
    echo 'email: '.$row['email']."\n";  // email: mike.o'[email protected]
}