mysql_real_escape_string还不够好？

Question

So using %27 you can just SQL inject even though data is sanitized with mysql_real_escape_string

%27) SQL INJECTION HERE %2F*

What to do?

Edit with example:

$sql = sprintf("SELECT *, MATCH(post) AGAINST ('%s*' IN BOOLEAN MODE) AS score FROM Posts WHERE MATCH(post) AGAINST('%s*' IN BOOLEAN MODE)",
                mysql_real_escape_string($_GET['searchterm']),
                mysql_real_escape_string($_GET['searchterm']));

$results = $db->queryAsArray($sql);

If you pass in %27) SQL INJECTION HERE %2F* to the searchterm querystring, I get outputted on the page:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BOOLEAN MODE)' at line 1

Thanks everyone for finding the problem in the db class..

Answer 1

Reasoning from the method name queryAsArray, it seems that you’re using this DbBase class from the comments of the MySQL functions manual page. If so, it’s the query method that removes the escape character from the escaped quotation marks:

function query($sql, &$records = null){
    $sql = str_replace(array('\\"', "\\'"), array('"', "'"), $sql);
    // …
}

Then it’s not a miracle that your example works (I simplified it):

$input = "', BAD SQL INJECTION --";

$sql = "SELECT '".mysql_real_escape_string($input)."'";
var_dump($sql);  // string(33) "SELECT '\', BAD SQL INJECTION --'"
//                      everything’s OK ↑

$sql = str_replace(array('\\"', "\\'"), array('"', "'"), $sql);
var_dump($sql);  // string(32) "SELECT '', BAD SQL INJECTION --'"
//                                Oops! ↑