input escaping & sanitizing

[elemelas]

Hi guys,

I have couple of forms which I'm now trying to sanitize. What I'm doing at the moment is to apply 3 functions to the input: strip_tags, htmlspecialchars & mysql_real_escape_string. Then each mysql query is done through PDO to minimize SQL injection risk.

For int values I'm running FILTER_VALIDATE_INT, with min & max value.

For <select>'s I have written whitelists, so the script will exit() when input isn't containted in the whitelist array.

So, here are my questions:

1. Are these 3 functions safe enough ? Or should I implement something else as well ? I'll probably create tokens for each form to prevent XSRF, but what else can I do to improve security on my website ?

2. Do I have to strip tags & escape the string on the whitelist inputs as well ? Because, theoretically, if user will doctor the form and input anything else than what I'm expecting, script won't process anyway. So I think there's no point, or am I wrong ?

3. Do I have to strip tags & escape the string on int values as well ? Because, if for example value will be smaller than 18 and larger than 99 script won't go through. Or am I wrong again ?

[NogDog]

If you are using PDO, you should probably be using prepared statements with bound parameters to take care of SQL injection issues. If so, you should not also be using mysql_real_escape_string(), as you would then be doubly escaping the inputs (and unnecessarily having to create a separate DB connection in order to use the mysql_real_escape_string() function).

The database couldn't care less if there are any HTML tags or HTML special characters in the data, so from that standpoint it may make more sense to do any strip_tags() and/or htmlspecialchars() filtering when outputting the data in a web page -- or, alternatively, validating inputs where you do not want to allow them and rejecting them as invalid before ever even getting to the point where you're inserting them into the DB.

As far as numeric values, one approach is to cast the input to the desired type:

$foo = (int) $_POST['foo'];
$bar = (float) $_POST['bar'];

[elemelas]

This is the way I'm currently inserting data to the db:

$sql = "INSERT INTO profile (email,pass) VALUES (:email,:hash)"; 

$q = $conn->prepare($sql);
$q->execute(array(':email'=>$email,':hash'=>$hash)) or die("Error");

So you reckon I should bind the parameters first ? And why is that safer ?

Also, is (int) or (float) better than FILTER_VALIDATE_INT ?

Thanks for any clues 🙂 cheers

BTW how to insert current time with PDO ?? now() doesn't seem to be working ...

[NogDog]

Bound parameters are not really "safer", but if you consistently use them with prepared statements, then they take care of the escaping for you so there is no need to call a separate function. Also, if you do it with PDO, then your application code does not have to worry about which DBMS you are using and how to escape inputs: PDO takes care of those details.

I would see filter_var() using FILTER_VALIDATE_INT (or whatever you're validating) more as part of the form validation, not for filtering values to prevent SQL injection. If, however, you are doing something where you just want to be quick-and-dirty about it and convert whatever was input into an integer without any sort of validation, casting it via b[/b] is probably the fastest way (there is no call to a separate function, nor any kind of if/else logic, etc.)

As far as bound parameters, PDO lets you bind them as you did via the execute() method's optional argument, or via the bind_param() method. The bind_param() would only be preferred if you want to use any of its additional parameters, such as the "data_type" specification, otherwise I am not aware of any functional reason to prefer one over the other.

I believe "NOW()" should work in your query, but it would just be a literal function call in the query, not any sort of input parameter.

$sql = "INSERT INTO profile (email,pass,date) VALUES (:email,:hash,NOW())";

[elemelas]

Well, i don't really wanna be quick-and-dirty, rather quick-and-safe 😉 will int() do the same job safetywise as FILTER ?

speaking about NOW():

$query = $conn->prepare('SELECT * FROM profiles WHERE date>NOW()');

doesn't work for me! doesn't give me any errors, just doesn't output any data. but exactly the same statement works fine with plain mysql ... just with PDO wouldn't output anything .. is it a bug or something ? I'm using PHP 5.3.0 ...

[NogDog]

b[/b] (not a function named "int()") will cast the following expression to an integer. You can therefore be guaranteed that it will be an integer, though depending on what the value was, it may end up being zero.

(int) '12abc' -> 12
(int) '12.345' -> 12
(int) '12' -> 0

It really comes down to what you want to do: just force the value into the type you want, or reject invalid values and don't process them (e.g. returning to the input form with an error message).

As far as NOW(), I don't really use PDO much at all, so I've not run into it if it is a bug. Stupid question: are you sure date is a date/datetime column? Maybe CURDATE() would work better if it's a date? (If it's a char/varchar or an integer field, all bets are off when using the MySQL date functions.)

[elemelas]

NogDog;10963439 wrote:

b[/b] (not a function named "int()") will cast the following expression to an integer. You can therefore be guaranteed that it will be an integer, though depending on what the value was, it may end up being zero.

(int) '12abc' -> 12
(int) '12.345' -> 12
(int) '12' -> 0

It really comes down to what you want to do: just force the value into the type you want, or reject invalid values and don't process them (e.g. returning to the input form with an error message).

that's a great explanation, thanks!

As far as NOW(), I don't really use PDO much at all, so I've not run into it if it is a bug. Stupid question: are you sure date is a date/datetime column? Maybe CURDATE() would work better if it's a date? (If it's a char/varchar or an integer field, all bets are off when using the MySQL date functions.)

I'm sure and it wouldn't be working with plain MySQL otherwise .. neither NOW() or CURDATE() are working through PDO, while they are completely perfect through mysql_query ...