Prevention of SQL injection in PHP
dev.digi-corp.com
dev.digi-corp.com
For those, like me, who are slaves to mysql for one reason or another, this is what I do.
1) mysql_real_escape_string 2) sprintf
I usually mix the two. Here's an example.
$format = "UPDATE `Users` SET `user_password` = '%s' WHERE `user_id` = %d";
$query = sprintf($format, mysql_real_escape_string($password), mysql_real_escape_string($user_id) );
Techincally, I don't have to do mysql_real_escape_string on the $user_id variable, since it is not based on user input and sprintf will automatically convert it to 0 if it isn't a number already (and there are no user_ids that equal 0 or 1 in this particular table)
(Before someone asks, all the passwords are salted and encrypted using SHA-256. Although, now that I think about it, they are in a binary format, which makes me wonder if I should use %b instead of %s... and since its in binary format already, I really don't need mysql_real_escape_string for the password either. Ugh )
It's got real prepared statements (or a reasonable facsimile thereof for databases that don't support them), and it lets you iterate directly over result sets. All sorts of handy stuff.
The developer who's writing this apparently hasn't ever tried SQL injection, though. If they had, they'd know that trying to tack on a second query with a semicolon doesn't work with PHP's mysql functions. The only real way to understand security flaws of this sort is to try exploiting them yourself.
http://shiflett.org/blog/2006/jan/addslashes-versus-mysql-re...
Pros: 1. Save on query parsing 2. Save on data conversion and copying 3. Avoid SQL Injection 4. Save memory on handling blobs
There are also a number of cons. Check out the article before you make this jump.