开发者

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

I have an email address mike开发者_StackOverflow社区.o'malley@stack.com 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'";


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.


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.


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\'malley@stack.com'. 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'malley@stack.com";
// 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'malley@stack.com
} 
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜