Is pg_escape_string or mysql_escape_string enough to sanitize a string?

Is pg_escape_string or mysql_escape_string eno开发者_JAVA百科ugh to sanitize a string before inserting data into a database table?

---

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.

---

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.

---

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.

---

(See comments elsewhere)

yes.

---

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