0

Is pg_escape_string or mysql_escape_string enough to sanitize a string before inserting data into a database table?

Elitmiar
  • 35,072
  • 73
  • 180
  • 229

5 Answers5

3

The word “sanitize” is highly questionable. It implies a worldview where certain characters are “bad” and have to be filtered at source. This is misguided.

Getting text in suitable format to go in an SQL query is about escaping out-of-band characters to their SQL literal form, not about removing “bad” characters. If you want to validate user input on entry to your application (eg. verifying a telephone number has no letters in it, or getting rid of unwanted control characters) then that's fine. But that's an application-specific validation concern, and an entirely different issue to anything to do with SQL-escaping or HTML-escaping. Those are output-stage concerns.

mysql_escape_string is potentially not enough to safely escape text for inclusion in an SQL string literal. On a connection that might be using some East Asian character sets as the encoding, or some non-default SQL syntax options, it will generate malformed strings that can permit SQL-injection. mysql_real_escape_string is better. However, parameterised queries avoid the issue and are to be preferred where available.

pg_escape_string uses the connection, like mysql_real_escape_string does, so I would expect it to be safe. But still, parameters! In pg_ you get pg_query_params so there's no excuse not to use them.

bobince
  • 528,062
  • 107
  • 651
  • 834
1

For the data - yes. Just don't forget to enclose it in quotes too.

But parametrized queries considered better, because escaping rules seems too complicated for average PHP programmer.

Note that either escaping or parameters has nothing to do with identifiers or operators. Say, field names cannot be sanitized at all. Escaping can't help with LIMIT parameters too.

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

No.

Blindly calling mysql_real_escape_string is not enough in order to prevent SQL injection attacks. From the manual:

mysql_real_escape_string() calls MySQL's library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a.

Note: mysql_real_escape_string() does not escape % and _. These are wildcards in MySQL if combined with LIKE, GRANT, or REVOKE.

Marijn van Vliet
  • 5,239
  • 2
  • 33
  • 45
  • so what? what you'd recommend then? – Your Common Sense Apr 29 '10 at 12:17
  • LIKE-escaping is another layer of escaping on top of string literal escaping. Failing to LIKE-escape isn't a security risk, but it means that if someone tries to do a search for “100% great!” they'll get all results with “100” followed by any number of characters followed by “ great!”. LIKE-escaping is tricky even once you've sorted the literal escaping problem. http://stackoverflow.com/questions/2106207/escape-sql-like-value-for-postgres-with-psycopg2 – bobince Apr 29 '10 at 14:03
-2

(See comments elsewhere)

yes.

symcbean
  • 47,736
  • 6
  • 59
  • 94
-3

Nope. You can also sanitize input with HTML Purifier http://htmlpurifier.org/ for xss attacks.

osm
  • 4,186
  • 3
  • 23
  • 24
  • 2
    HTMLpurifier and ___real_escape_string() do completely different things – symcbean Apr 29 '10 at 11:16
  • 2
    And even for preventing XSS attacks, running HTML Purifier over the input is entirely the wrong thing: it will mess up valid input and won't protect against all attacks. The right way to stop XSS when using text strings is to use `htmlspecialchars()` at the output stage. HTML Purifier is useful only for those rare cases where you need to let the user input literal markup. – bobince Apr 29 '10 at 11:42