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?

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, Apr 03 '09 at 16:33
Duplicate: http://stackoverflow.com/questions/306668/are-parameters-really-enough-to-prevent-sql-injections, http://stackoverflow.com/questions/650455/c-parameterized-query-mysql-with-in-clause — S.Lott, Apr 03 '09 at 16:36
@S.Lott: I don't think those count as dupes, since they assume the answer to this one. — Jon B, Apr 03 '09 at 16:38
Near duplicate: http://stackoverflow.com/questions/139199/can-i-protect-against-sql-injection-by-escaping-single-quote-and-surrounding-user — S.Lott, Apr 03 '09 at 16:38
@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. — S.Lott, Apr 03 '09 at 16:42
@S.Lott: I'm just saying that I might search on SQL injection, being unaware that parameterized queries are they way to go. I wouldn't search for info on parameterized queries if I didn't know that's the best way to prevent injection. — Jon B, Apr 03 '09 at 16:45

score 15 · Accepted Answer · answered Apr 03 '09 at 16:33

15

Parameterized Queries

answered Apr 03 '09 at 16:33

Chad Birch

73,098
23
151
149

+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. – Sedat Kapanoglu Apr 03 '09 at 16:38
+1: always works -- makes injection actually impossible. String manipulation only makes it unlikely for all the cases we tested. – S.Lott Apr 03 '09 at 16:43
+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!) – John Gietzen Apr 03 '09 at 18:51

score 1 · Answer 2 · edited May 23 '17 at 12:01

1

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.

edited May 23 '17 at 12:01

Community

1
1

answered Apr 03 '09 at 16:33

lc.

113,939
20
158
187

score 0 · Answer 3 · answered Apr 03 '09 at 16:37

0

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.

answered Apr 03 '09 at 16:37

Alnitak

334,560
70
407
495

score 0 · Answer 4 · answered Apr 03 '09 at 18:41

0

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.

answered Apr 03 '09 at 18:41

K. Brian Kelley

1,597
9
8

score -1 · Answer 5 · edited Jan 30 '13 at 14:11

-1

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".

edited Jan 30 '13 at 14:11

jenzz

7,239
6
49
69

answered Jan 30 '13 at 13:53

John Carter

11
1
3

HTML escaping have to be used for the HTML, not SQL. No HTML escaping will make space into %20 though – Your Common Sense Jan 30 '13 at 15:12

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

See also

5 Answers5

Linked