Safest modern filter for database insertion?

Question

Temporarily ignoring prepared statements and other way of actually doing the insertion into a database, what is the best current way of sanitizing input before it gets inserted?

function filter($s) { return htmlentities(addslashes($s)); }

or

function filter($t) { return mysqli_real_escape_string($t); }

In this instance `mysqli_real_escape_string` **but** binding parameters to prepared statements is much better : http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — CD001, Jun 07 '16 at 08:21
`mysqli_real_escape_string` is still the better method, but it is terrible compared to proper use of prepared statements. No form of string escaping should be tolerated in modern SQL applications. — Chris, Jun 07 '16 at 08:21
`htmlentities` is totally wrong. The only purpose of that is to escape HTML when displaying it on a web page, it has nothing to do with databases. — Barmar, Jun 07 '16 at 08:35
being that sometimes content from the database has to be output on the page, should `mysqli_real_escape_string` and `htmlentities` be used in together for such data? — Meghan, Jun 07 '16 at 08:37
No, you should html encode content when displaying data on a html page, not when inserting the data to database. — 1615903, Jun 07 '16 at 08:56
Also, I don't see the point in this question. Prepared statements is the **only** way to protect against SQL injection, why would you not want to use that? — 1615903, Jun 07 '16 at 09:01

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

Temporarily ignoring prepared statements and other way of actually doing the insertion into a database,

Okay, let's put best practices that actually achieve security aside...

what is the best current way of sanitizing input before it gets inserted?

This is a curious question! If you have mysqli, you can use prepared statements, so I don't understand the reason why anyone wouldn't use them?

Granted, there are a narrow set of use-cases where you need to accept user data and combine it with a SQL query. LIMIT $foo is a classic example (easiest solution: use PHP 7 and only accept an int to that parameter).

Another example is SELECT * FROM table WHERE id IN (2, 3, 4, 5). Once again, casting user input to an integer removes most of the attack surface here.

Integers are easy. What about strings?

Well, read this at least once. You should be very familiar with charset attacks and ensure you're using a safe charset, safely.

As long as you follow the prescribed advice, yes, you can safely use mysqli_real_escape_string() or PDO::quote().

But seriously, use prepared statements. You're less likely to make a mistake if you separate data from code.

function filter($s) {
    return htmlentities(addslashes($s));
}

Not only is this a bad idea for preventing SQLi, it's a bad idea to attempt to strip XSS on insert.

In closing, SQLi and XSS are solved problems, and the tools and strategies for preventing SQLi and the tools for preventing XSS have virtually no overlap. Confuse them at your own peril.

Safest modern filter for database insertion?

1 Answers1