What is the best solution for SQL injection security on MySQL?

Question

What is the best function to run my strings through to ensure that MySQL injection is impossible?

Also, will it require running it through another function on the way out to make it display correctly?

See also

Are Parameters really enough to prevent Sql injections?
C# Parameterized Query MySQL with in clause
Can I protect against SQL Injection by escaping single-quote and surrounding user input with single-quotes?

• S.Lott: What ORM or access layer are you using? What language are you using? For most ORM's, this is trivially handled for you. If you use the JDBC driver properly, this is trivially handled for you. Please provide the language and components you're using.
• S.Lott: Duplicate: stackoverflow.com/questions/306668/…, stackoverflow.com/questions/650455/…
• user27414: @S.Lott: I don't think those count as dupes, since they assume the answer to this one.
• S.Lott: Near duplicate: stackoverflow.com/questions/139199/…
• S.Lott: @Jon B: My point is that the question is unsound. SQL Injection is impossible with parameterized queries -- no trust involved. Any string manipulation will work for all the tested cases; you have to trust that they tested all the cases. Consider taking out the element of trust.

Answer 1

Parameterized Queries

• Sedat Kapanoglu: +1 that's the way to go. Not just because it avoids SQL injection but it also allows RDBMS to do better query execution plan caching.
• S.Lott: +1: always works -- makes injection actually impossible. String manipulation only makes it unlikely for all the cases we tested.
• John Gietzen: +1: Mush better than trusting your own implementation to not have any edge cases or special syntax vulnerabilities. This also prevents the need to use HTML entities in the database. (I'm looking at you phpBB!)

Answer 2

A parameter function.

Humor aside, I mean don't dynamically execute user-entered content as SQL if you can at all avoid it. Pass everything as parameters, and reference them from your query instead. See Chad Birch's answer for a good link explaining this.

Answer 3

As Chad says, always use parameterized queries to avoid SQL injection.

To answer the second half of your question, if your output is to a web page then always escape any special HTML characters (&, <, >) to protect against script injection.

Answer 4

Add to parameterized queries the use of input validation within the application. Never trust that the input is clean. Check it. For instance, if it's supposed to be an integer, check to make sure it converts to a numeric value without issue.

Answer 5

In PHP the best way is to use HTML escaping on strings.
It turns special characters into HTML compliant characters.

Example: " " (space) transforms into "%20".

• Your Common Sense: HTML escaping have to be used for the HTML, not SQL. No HTML escaping will make space into %20 though