2

I have been learning PHP and MySQL over the last few weeks, and I'm just now hearing about prepared statements and PDO/mysqli. I did some reading and people are saying that stuff like:

$getFromDatabase = mysql_query("SELECT * FROM table WHERE userid='$id'");
while($row = mysql_fetch_assoc($getFromDatabase)){
    stuff();
}

...won't work anymore. I use stuff like that pretty frequently in my code. I also am reading a lot about how prepared statements are much better protection against injection. I've got a thorough understanding of how it is better, but is it so much better that it's worth switching away from mysql_real_escape_string()? Is it necessary to switch to Mysqli or PDO? In what situation could mysql_real_escape_string() be bypassed where a prepared statement couldn't?

ebol4
  • 152
  • 1
  • 1
  • 10
  • It works for now... The `mysql_*()` functions are facing a _future_ deprecation. It is advisable to start moving away from them. – Michael Berkowski Aug 29 '12 at 01:30
  • 1
    And the main reason prepared statements are superior to `mysql_real_escape_string()` is _you_. You can, and at some point, will forget to call it. We've all done so. – Michael Berkowski Aug 29 '12 at 01:31
  • P.S. There should be no single quotes around `$id`. It's a number, not a string literal. You don't quote numbers. – Dan Grossman Aug 29 '12 at 01:32
  • @DanGrossman Well what if the ID is a GUID? – Waleed Khan Aug 29 '12 at 01:33
  • 1
    possible duplicate of [Are PDO prepared statements sufficient to prevent SQL injection?](http://stackoverflow.com/questions/134099/are-pdo-prepared-statements-sufficient-to-prevent-sql-injection) – Michael Berkowski Aug 29 '12 at 01:33
  • MySQL doesn't care if you quote numbers. It just shrugs and converts as necessary. – tadman Aug 29 '12 at 01:52

1 Answers1

7

It's necessary because mysql_query is, in software terms, an ancient artifact. It's the Model T of MySQL database interfaces, clunky, unreliable, and downright dangerous if not used exactly as you should. If you're not extremely careful you will make a mistake, and even a tiny mistake will not go un-noticed if someone uses an automatic SQL injection bug detection tool on your site.

Basically you're living on borrowed time with mysql_query.

If you use mysqli or PDO and are diligent about using placeholders then the risk of a SQL injection bug is very low. It may take about half an hour to figure out how to convert your old code to these new methods, there really isn't a steep learning curve, and that knowledge will save you a lot of trouble in the future. Converting existing code usually isn't too big a deal if you use mysqli and basic placeholders. I bet you even find some serious bugs while patching things up.

In terms of benefits, you won't need to make any mysql_real_escape_string calls, you can just use bind_param, and you won't have to worry about missing an escape on one of your variables. It's actually a lot less work in the long-run.

Additionally, using ? or a named placeholder like :id makes your queries significantly easier to read, debug and maintain. Further, you can use the same statement repeatedly and bind different values to it, ultimately making your application faster.

It is possible to write safe code with mysql_query but why would you? The interface is listed as deprecated, which is the preliminary stage to it being removed from PHP entirely. If you want a future-proof application, it's best to use one of the supported interfaces.

tadman
  • 208,517
  • 23
  • 234
  • 262