Prevention of SQL injection in PHP(dev.digi-corp.com)
dev.digi-corp.com
Prevention of SQL injection in PHP
http://dev.digi-corp.com/2009/05/prevention-of-sql-injection-with-php/
14 comments
Yikes, that's a lot of bad advice for one article. 3 out of 4 methods it recommends for preventing SQL injection should be avoided and it doesn't even mention prepared statements at all.
ok ok guys, i have updated the post.
While we're at it: it also fails to understand the difference between addslashes() and mysql_escape_string(). (It's not just a matter of whether you're assigning the result to a variable or not!)
While you're still at it: get rid of the quotes entirely, just htmlentities( ) where appropriate.
They should also be suggesting straight up data validation before sticking anything in the db. No reason to let things get that far if you're expecting a number and they give you garbage.
Like you mention, I cannot understand how a problem like this that has been solved gets such a bad solution.
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 )
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 )
You should really look into using PDO: http://php.net/pdo
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.
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.
As the blog comments are hinting, this is a terrible way to achieve "prevention of SQL injection". One missed addslashes() and you're toast. The real answer is to use prepared queries through mysqli, PDO, ADODB, or similar.
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.
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.
addslashes is NOT an appropriate way to prevent SQL injection attacks. Chris Shiflett explained the addslashes vulnerability years ago:
http://shiflett.org/blog/2006/jan/addslashes-versus-mysql-re...
http://shiflett.org/blog/2006/jan/addslashes-versus-mysql-re...
Hi, I'm still a little confused about the advantage of prepared statements. No explanation I've seen so far has actually explained why they are good, but my thinking was that the developer does not have to remember all of the dangerous characters to escape because the prepared statement stuff do that for you. But now I'm reading here http://dev.mysql.com/tech-resources/articles/4.1/prepared-st... that the advantage of paramterization is separating the SQL logic from the data supplied. Why would that matter? You can still supply data such as ' OR 'x'='x
http://www.mysqlperformanceblog.com/2006/08/02/mysql-prepare...
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.
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.
@ the OP you might want to go ahead and update your blog -- potential clients googling your company might stumble across it and run away in fear ;)
its kind of first time for me dude. i have updated the post in any case.
One of those solutions still leaves you vulnerable to other SQL injection methods. While it technically prevents those false logins, you're still wide open to other forms of SQL injection. I'm not sure why something like that would even be mentioned.