I'm wondering what's the best way to celar input data before inserting it into a mysql database.
There are a lot of function: trim, addslashes, mysql_real_escape_string and so on.
At this moment i'm using this simple function:
function filter($var){
$data = preg_replace('/[^a-zA-Z0-9]/','',$var);
$data = trim(addslashes($data));
return $data;
}
What's the best way to do it? Thanks
Using mysql_real_escape_string() is enough for security reasons. Another way to do it is using prepared statements.
But you should check what information in what type you want in your database. There are several functions and language constructs you could use: Typecasts, filter_*() functions, int_val(), abs(), trim(), and a whole lot more.
I suggest you take a look at prepared statements that pretty much protect you against all form of SQL Injection.
The parameters to prepared statements don't need to be quoted; the driver automatically handles this. If an application exclusively uses prepared statements, the developer can be sure that no SQL injection will occur (however, if other portions of the query are being built up with unescaped input, SQL injection is still possible).
The best thing is to do multiple things:
The validation is to check whether the data you've got makes any sense. For instance if you expect a birth date you check whether the format is correct and maybe even whether the date amkes sense. This not only has security benefits but also prevents some (not all) errors of wrong data. The tools there depend on the case, regular expression (preg_match) are often a good choice.
Cleaning data is often not really needed, but nice, for instance if a user types in some value use trim() to split of some whitespaces, which might be mistakes from copy and paste or such. This has no security benefit but improves the overall quality of your data. Which is good.
Both of these things should be done early in your script. While "early" depends on your achitecture. Sometimes it makes sense to clean first an validate then or doing it at once (preg_replace)
Then when sending data of to a database or putting it in HTML or any of these things oyu have to escape it accordingly to the system you are using. You should do that for all data, even when you verfied the format beforehand to be on the safe side. When talking to mysql these are the real_escape_string functions for instance, for HTML it is htmlentities() or htmlspecialchars(). with databases it is also a good idea too look into prepared statements, either PDO->prepare + execute() or mysqli->prepare() +execute()
addslashes()is superfluous in this case. Also, if your app accepts unicode input, brace yourself for b0rken entries in your database.