A better way to Protect Your PHP/MySQL Queries from SQL Injection
By Justin Silverton
In a recent article I saw today about php/mysql security, called “protecting your php/mysql queries from sql injection”, The following method was described as a safe way to execute mysql queries (preventing what is known as a sql injection attack).
// This is a vulnerable query.
$query = "SELECT * FROM products WHERE name='$productname'";
mysql_query($query);
// This just uses mysql_escape_string
$query = sprintf("SELECT * FROM products WHERE name='%s'",
mysql_real_escape_string($productname));
mysql_query($query);
This will work, with select and insert statements, but will not work with statements such as: LIKE, GRANT, or REVOKE. This is a more secure way of preventing SQL injection attacks.
// This query is more secure
$query = sprintf("SELECT * FROM products WHERE name='%s'",
addcslashes(mysql_real_escape_string($productname),'%_'));
mysql_query($query);
7 Comments so far
Leave a reply
Isn’t there some additional rules that addcslashes applies that sql doesn’t need? Also, why wouldn’t it work with other SQL queries?
Or you could use the PDO object or stored procedures…
I think these are better ways…
Why not use bind parameters? Much more performant and much faster.
“why wouldn’t it work with other SQL queries?”
There’s no reason why it couldn’t.
Of course there’s always mysql_escape_string(), though it doesn’t escape % charachters - so you’ll want to sanitise these if you don’t want wildcard string searching.
You’ll also want to check that magic quotes isn’t on (which automatically does an addslashes to all GET/POST input), because otherwise you’ll end up with double escaping!
It seems you try to implement your own prepared statements mechanism.
Is `addcslashes()` aware of character sets? The MySQL C API will perform correct string escaping, but it requires the database connection in order to use the character set, so I’m not sure how you can get around that issue without using the MySQL API in PHP (unless you’re just ignoring the charset issue).
Of course, this is all moot if you’re using PHP 5, in which case you should probably be using PDO and prepared statements.