1

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

Improve this question
2

5 Answers 5

Reset to default
4

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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.

Improve this answer

Comments

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.

Improve this answer

2 Comments

so what? what you'd recommend then?
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. stackoverflow.com/questions/2106207/…
-2

(See comments elsewhere)

yes.

Improve this answer

Comments

-3

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

Improve this answer

2 Comments

HTMLpurifier and ___real_escape_string() do completely different things
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.