php - SQL-safe method

Thursday, 29 June 2017

php - SQL-safe method

Answer

Context: I'm trying to convince a friend to switch to using parameterized queries to prevent SQL injections and other malicious attempts as that is the standards these days but he has this mentality of "If it's not broken, don't fix it."

Here's the code he currently uses:

function sql_safe($text) {
    return str_replace("'", "''", $text);
}

Is there a way for me to break this function to illustrate to him that this approach is not advisable anymore? I've been trying but I can't break it myself so now I'm turning to you guys for help.

Additional Info

It's being used as a general means to protect the system from SQL injections so that user inputs are escaped properly. But I feel like his approach could break at certain scenarios which I haven't figured out yet.

Answer

Here's your code:

function sql_safe($text) {
    return str_replace("'", "''", $text);
}
echo "SELECT * FROM db WHERE field = '" . sql_safe($argv[1]) . "';\n";

And here's the most obvious way of breaking it:

$ php ./x.php "\' OR TRUE; -- MySQL"
SELECT * FROM db WHERE field = '\'' OR TRUE; -- MySQL';

has covered the topic of SQL injection extensively over the years. See for example Can I protect against SQL Injection by escaping single-quote and surrounding user input with single-quotes? . There's a neat trick in there that exploits "maximum length of string" to truncate just one of the replacement ''s.

Blog

Thursday, 29 June 2017