To understand the validation of user's data for prepared statements

Question

I read that you do not need to validate or sanitize user's input if you use prepared statements.

This however does not make sense to me in the following example.

The user gives his email address.

I normally run this

Validation code

 // to validate data
 if (!filter_var($_POST['email'], FILTER_VALIDATE_EMAIL)) {
     header("Location: index.php");
     die("Wrong email-address");
 }

The statement says that we do not need to validate data if we use prepared statements, as follows

Code without the validation code

 // to get email-address nad passhash from the db
 $result = pg_prepare($dbconn, "query2", 'SELECT email, passhash_md5
     FROM users WHERE email = $1
     AND passhash_md5 = $2;');
 $result = pg_execute($dbconn, "query2", array($_POST['email'], $_POST['password']));
 if(!$result) {
     echo "An error occurred";
     exit;
 }

I am not sure if we need the validation code or not in the last code, since we use pg_prepare and pg_execute.

Do you need to consider validating and sanitizing user's input if you use prepared statements?

Answer 1

Prepared statements will take care of malicious intent, but it's still up to you to verify that your user is inputting what you are expecting.

Example, if you have a form that is asking for a telephone number, a user entering "jkl;asdgfjkladg" will do no harm whatsoever to your database, but the data is beyond useless.

Never trust your users to do what they're supposed to do. If you're accepting input from users, validate to make sure they are doing it right.

Answer 2

You still have to validate your data but you do not have to sanitize it.

Using prepared statements prevents insertion of malicious code but does not check if its form makes sense.

Answer 3

It's important to separate validating and sanitizing.

Validating is making sure that the user has entered the correct type or format of data. For example, on a web form someone could hack the response of a select input to submit a value that doesn't appear in the selection you provided. Validating could catch this error, and provide a suitable error message back to the user. The user could also enter a string into a field that expects a number value. If you do not check this before attempting the query, the query may fail and it will be tougher to determine the cause and provide the user with an error message, or the query may succeed silently with bad data.

Sanitizing is making sure that the data will not cause harm when added to the database (or later displayed on the website, causing an XSS attack or such). The query "INSERT INTO people (names) VALUES('$name')" will fail if $name = "O'Reilly", but a prepared statement will automatically escape the single quote when binding the string as a parameter. When using different character sets this is especially important, as there may be more than just the quote and slash characters that can cause problems, possibly allowing an SQL injection attack.

In order to check that the user is actually entering an email address, you need to keep the validation code, since PostgreSQL has no idea that the data entered is supposed to be an email address, and will store any string you provide to it.