3

Prepared statements add a significant amount of code...yet I keep hearing mentions of using them...what value is added by going from 1 line of code to about 6? Is this simply to protect against SQL injection?

php.net on prepared statements here

Dharman
  • 30,962
  • 25
  • 85
  • 135
  • 3
    You can wrap these 6 lines of code into a method and also have just one line. – Mchl Sep 11 '11 at 19:27
  • This question has been discussed before, but I'm having a tough time locating an appropriate duplicate to link :( – Michael Berkowski Sep 11 '11 at 19:36
  • i linked to one above stating that you don't need to use mysqli_real_escape_string() with prepared queries..which is good b.c. my serverice provider had magic quotes set to the wrong value...meaning I would need a significant amount of code to use that particular function... –  Sep 11 '11 at 19:38
  • It is not fair to state that going from regular statement to a prepared statement is done at the expense of 5 additional lines of code. First of all, extra code is not necessarily a bad thing if the overall code is more readable and secure. Further more, many of the lines you use in your example are either required by a regular statement as well, or have nothing to do with the prepared statement at all. – Tommy Andersen Sep 11 '11 at 19:52

2 Answers2

7

Prepared statements offer excellent protection against SQL injection.

In addition to SQL injection protection, prepared statements offer reduced load on the database server when the same query is to executed multiple times, such as in an INSERT loop. The statement is only compiled once by the RDBMS rather than needing to be compiled each time as it would in a mysql_query() call.

Different APIs require varying amounts of code to execute a prepared statement. I find that PDO can be a little less verbose than MySQLi, if for example your situation permits the use of implicit parameter binding inside the execute() call. This only works, if all your params can be evaluated as strings though.

// PDO implicit binding example:
// Not many lines of code if the situation allows for it
$stmt = $pdo->prepare("SELECT * FROM tbl WHERE col1=? AND col2=? AND col3=?");
$stmt->execute(array($val1, $val2, $val3));
Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
  • this comparison for mysql_query...is the same for mysqli_query()...i just updated to this a few weeks ago...now I'm about to update again. –  Sep 11 '11 at 19:31
  • @Chris Yes the same comparison applies to `mysqli_query()`. Statement must be compiled on each call by the RDBMS – Michael Berkowski Sep 11 '11 at 19:34
  • Right but even wrong: prepared statements do not offer any "protection". Its just that parameter binding interface that offers some kind of protection. But prepared statements are the wrong tool! Prepared statements are even bad in many situations. Performance-wise as well as ressource-wise. I do not understand that PDO developers did not fix their misconception. Its confusing. – Frunsi Jan 10 '16 at 07:09
4

It's not fair to say that prepared statements cause 1 line of code to explode to 6. Actually, to use one you need just 2 lines: one to prepare the statement, and one to bind the parameters. Any other code you write (execute query, bind results, fetch results, etc.) would also be needed even if you didn't use prepared statements.

So in essence we are talking about what one additional line of code buys you. It buys you two things:

  1. Protection against sql injections (which also includes protection against non-malicious malformed queries, e.g. preventing your query from breaking if an injected variable contains a single quote)
  2. Possible performance benefits, if you end up executing the same prepared statement for different injected values.

Point #2 may not always apply, but consider that point #1 also saves you the necessary trouble of manually escaping the values to be injected in your query. This would be additional code (even if you can do it inline on the same line) that you would need to write yourself if not using prepared statements.

As I see things, we can conclude that with prepared statements you end up getting security and possibly performance for free.

Jon
  • 428,835
  • 81
  • 738
  • 806
  • i think you get security and readablity..but if it is just for one query and not a loop ...i think their is a very small performance drop...per above the benefits come when you call it multiple times... –  Sep 11 '11 at 19:41
  • @ChrisAaker: Initially you were concerned about writing more code than necessary (which I don't think is what ends up happening in practice), so I did not tackle performance. As for the latter, IMHO talking performance vs. security is completely the wrong way to view things unless you have the traffic of Facebook. – Jon Sep 11 '11 at 19:44