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.