0

I have a (relatively) simple interactive web site. I do not run queries in a loop. All inputs are either strings, integers or images. I confirm all integer and image data types and use mysqli_real_escape_string() on all strings.

Putting aside evangelism, what advantage would I get out of using prepared statements with parameterized queries?

Other answers I've found don't address this specific comparison.

Frank A.
  • 51
  • 7
  • it would help your application against XSS attacks and SQL Injection especially if your application is online or public – unixmiah Apr 15 '16 at 18:24
  • 2
    You don't run any risk of accidentally introducing SQL injection vulnerabilities because you forget to escape things. You also don't run the risk of accidentally using `mysql_real_escpape_string` somewhere it doesn't actually protect against injection like a LIMIT clause. – Chris Apr 15 '16 at 18:26
  • As I understand this answer, the two approaches are equally effective against sql injection but prepared statements enforce a discipline that helps to avoid mistakes. Sounds like an advantage to me. I'm not clear on how XSS attacks are involved in db queries. – Frank A. Apr 15 '16 at 20:41
  • @FrankA. I agree with you there. XSS doesn't really come in to it. But again, there are certain places where `mysql_real_escape_string` is not effective. You might have already seen it, but this gives a quite interesting set of possible problems: https://stackoverflow.com/questions/5414731/are-mysql-real-escape-string-and-mysql-escape-string-sufficient-for-app-secu – Chris Apr 15 '16 at 21:07
  • OK. Although my particular application does not expose me to many of the issues you describe, I'm convinced that prepared statements are a good way to go. Now I have questions about implementation and avoiding unwanted escapes but that calls for a different question. – Frank A. Apr 16 '16 at 18:36

1 Answers1

0

I'll try an excerpt from my Hitchhiker's Guide to SQL Injection prevention:

Why manual formatting is bad?

Because it's manual. Manual means error prone. It depends on the programmer's skill, temper, mood, number of beers last night and so on. As a matter of fact, manual formatting is the very and the only reason for the most injection cases in the world. Why?

  1. Manual formatting can be incomplete.
    Let's take Bobby Tables' case. It's a perfect example of incomplete formatting: string we added to the query were quoted but not escaped! While we just learned from the above that quoting and escaping should be always applied together (along with setting the proper encoding for the escaping function). But in a usual PHP application which does SQL string formatting separately (partly in the query and partly somewhere else), it is very likely that some part of formatting may be simple overlooked.

  2. Manual formatting can be applied to a wrong literal.
    Not a big deal as long as we are using complete formatting (as it will cause immediate error which can be fixed at development phase), but combined with incomplete formatting it's a real disaster. There are hundreds of answers on the great site of Stack Overflow, suggesting to escape identifiers the same way as strings. Which is totally useless and leads straight to injection.

  3. Manual formatting is essentially non-obligatory measure.
    First of all, there is obvious lack of attention case, where proper formatting can be simply forgotten. But there is a real weird case - many PHP users often intentionally refuse to apply any formatting, because up to this day they still separating data to "clean" and "unclean", "user input" and "non-user input", etc. Means "safe" data don't require formatting. Which is a plain nonsense - remember Sarah O'Hara. From the formatting point of view, it is destination that matters. A developer have to mind the type of SQL literal, not the data source. Is this string going to the query? It have to be formatted then. No matter, if it is from user input or just mysteriously appeared amidst the code execution.

  4. Manual formatting can be separated from the actual query execution by a considerable distance.
    Most underestimated and overlooked issue. Yet most essential of them all, as it alone can spoil all the other rules, if not followed.
    Almost every PHP user is tempted to do all the "sanitization" in one place, far away from the actual query execution, and this false approach is a source of innumerable faults:

    • first of all, having no query at hand, one cannot tell what kind of SQL literal this certain piece of data is going represent - and thus violate both formatting rules (1) and (2) at once.
    • having more than one place for santization, we're calling for disaster, as one developer would think it was done by another, or made already somewhere else, etc.
    • having more than one place for santization, we're introducing another danger, of double-sanitizing data (say, one developer formatted it at the entry point and another - before query execution)
    • premature formatting most likely will spoil the source variable, making it unusable anywhere else.

  5. After all, manual formatting will always take extra space in the code, making it entangled and bloated.

A properly implemented parametrized query can make your code unbelievable short. A pseudo-code to demonstrate:

$data = DB::call("SELECT * FROM t WHERE foo=? AND bar=?", [$foo, $bar])->fetchAll();

This single line of code will get you an array of rows from the database. How many lines will it take with your manual formatting?

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345