0

Possible Duplicate:
Best way to stop SQL Injection in PHP

I'd like a simple way to validate against the possibility of SQL injection when using a MySQL database through PHP (i.e. to ensure a user-provided name in a $_POST contains no sql sub-queries).

What I'm looking for doesn't necessarily have to be industrial strength or unbreakable (though it would be nice admittedly) - its more or less for a personal project to teach myself things.

So, is there a standard, somewhat simple way of doing this in PHP? (I'm assuming there has to be). I know I could run a pile of hand-crafted regular expressions to check for SQL in my PHP-provided strings, but I can't believe that's the best way!

Community
  • 1
  • 1
John Humphreys
  • 37,047
  • 37
  • 155
  • 255
  • 1
    You should be looking for something "unbreakable", actually. Otherwise, there is not much of a point... – Thilo Nov 27 '11 at 01:15
  • I agree that it's a duplicate, I didn't see that question before. I'll accept an answer when I can since it won't let me delete it now that there's answers. – John Humphreys Nov 27 '11 at 01:18

4 Answers4

4

Use prepared queries with PDO. Read the php.net docs on them.

http://php.net/manual/en/book.pdo.php

http://www.php.net/manual/en/pdo.prepared-statements.php

Galen
  • 29,976
  • 9
  • 71
  • 89
0

Several rules of testing:

  1. Check is_numeric() if a numeric was expected (or is_string(), is_array(), is_object(), etc...)
  2. Use mysql_real_escape_string() on inputs and textareas.
  3. Use Sanitize filters in addition.
Josh Ferrara
  • 757
  • 2
  • 9
  • 25
GG.
  • 21,083
  • 14
  • 84
  • 130
-1

Try mysql_real_escape_string().

Josh Ferrara
  • 757
  • 2
  • 9
  • 25
Bill
  • 3,059
  • 3
  • 31
  • 47
  • 1
    Um... How so? If the OP writes a proper query for the mysql_query() method, and only need to make sure that user given data are clean then yes, mysql_real_escape_string is all they need to ensure that SQL injection will not happen because of user inputs. According to php.net "This function must always (with few exceptions) be used to make data safe before sending a query to MySQL." The only exception I can think of is BLOB data and/or he is using user input to modify the ACL of his database. – Bill Nov 27 '11 at 08:46
  • mysql_real_escape_string **doesn't "clean" anything.** Nor making any data "safe". Go figure. – Your Common Sense Nov 27 '11 at 08:57
  • 1
    No it certainly doesn't "clean" anything, whatever that means. As php.net points out, it "escapes special characters in the unescaped_string, taking into account the current character set of the connection so that it is safe to place it in a mysql_query()." – Bill Nov 27 '11 at 09:01
  • 2
    @Col.Shrapnel: OP asks about preventing SQL injections, not validating user input. `mysql_real_escape_string()` most certainly does that. – kba Nov 27 '11 at 17:18
-1

to ensure a user-provided name in a $_POST contains no sql sub-queries

This is absolutely wrong approach.

  1. it will help nothing, as injections aren't limited with subqueries
  2. it can falsely alarm at the honest data.
  3. it is useless, as there are other mechanisms, regular ones, to make injection impossible.
  4. not to mention 2-nd layer injections

The whole idea of validating makes very little sense.

A database intended to keep any data, with no limitations. Why limit yourself?

So, is there a standard, somewhat simple way of doing this in PHP?

Sure.

But only in terms of creating queries, not in terms of validating inputs.
So, here goes a very simple set of rules

As long as all variable parts goes into query either via

  • some placeholder - a proxy, a variable to represent the actual data (and on the assumption that placeholder being processed properly).
  • or chosen from some whitelist, a set of possible values hardcoded in your code -

one can be sure that no injection can be possible.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345