When are PDO params sanitized

PHP
1

I use PDO, just trying to understand it a bit more. So, using prepared statements means my variables are sanitized, but when does PDO sanitize data?

I thought preparing a statement sanitized data, but the code below is preparing the statment, not variables.


$sql = "UPDATE `users` SET `dashboard_order` = :order WHERE `user_id` = :id";
		
$stmt = $database->conn->prepare($sql);
		
// perform query
$stmt->execute(array(
	':order' => $_POST['data'],
	':id' => $user_id)
);

so, execute does the sanitation?

2

Pretty much. It’s not really sanitation though. Sanitation implies examining the posted data and removing anything nasty.

With prepared statements, the database server (i.e. mysql) compiles your sql statement then basically adds in the posted data. This means that anything nasty in the posted data does not get a chance to impact the sql compiler. So the posted data is never really sanitized but you get the same benefit.

3

Your data gets sanitised when you sanitise it after reading it from the database. You should always sanitise data to ensure that it hasn’t been tampered with within the database (unlikely but possible).

Using prepare does nothing to sanitise the data being read from the database as it assumes that whatever is in the data is valid - which if the database hasn’t been tampered with should be the case as you should have validated the data when it was first input long before it was inserted into the database.

Input processing

Validate all user input before processing it
Sanitise all other data read in to ensure that if it has been tampered with that it can’t do any harm

Output Processing

Keep data separate from code where ever possible - such as using Prepare when writing to the database
Escape and data where keeping it separate from the code is not possible - such as outputting HTML

From the question it appears that you are getting some of these four completely separate processes mixed up.

4

HUH???:rolleyes: If you sanitize the data or was sanitized after getting it from the database then it’s too late.

"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). "

That was a direct quote from php.net.You only have to sanitize unescaped input as the quote says, meaning query that is being built-up (example a search). If you know you are just using prepared statements and aren’t monkeying around with (building up) the query or taking short-cuts there is no need to sanitize the user’s input. Though if you want be absolutely sure there would be nothing wrong in sanitizing the user’s variables.

5

[QUOTE=Strider64;5661880]HUH???:rolleyes: If you sanitize the data or was sanitized after getting it from the database then it’s too late. [/qoute]

No it isn’t - sanitising is an INPUT process you use when you expect the data should already be valid and want to be sure. The alternative to sanitising (stripping out invalid characters) is validation where you produce an error message if it isn’t valid. Leaving sanitising or validating until you are outputting something means you left it too late and y0ur entire code is compromised.

Escaping is an OUTPUT process that has nothing to do with sanitising. Escaping is what you do with VALID data where that data is to be jumbled with code.

Escaping and sanitising have nothing whatever in common except that they both get used in programming.

There is no mention of sanitising in this quote from php.net - it is talking only about escaping.

"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). "

6

I agree with you on this. I don’t know of anyone else who’s concerned with sanitizing data that comes from their own database. Typically anything from your own database is considered trusted data.

That’s correct. For the purposes of preventing injection, all that’s needed is to properly escape/prepare.

7

“No it isn’t - sanitising is an INPUT process you use when you expect the data should already be valid and want to be sure. The alternative to sanitising (stripping out invalid characters) is validation where you produce an error message if it isn’t valid. Leaving sanitising or validating until you are outputting something means you left it too late and y0ur entire code is compromised.”

If the data gets into the database (which it won’t if you use prepared statements and don’t build up the query) your are opening yourself up to SQL injection (php.net even states it). I think we’re talking the same lingo only in different ways for you state 'sanitizing (btw you misspelled the word) is an INPUT process and if you don’t trust the USER’S INPUT YOU SANITIZE IT (I don’t know why you would trust user’s input). I give you wouldn’t want to sanitize the password until after you validate the password, but using prepared statements properly in the first place leaves this debate a mute point in my opinion. I also said Though if you want be absolutely sure there would be nothing wrong in sanitizing the user’s variables.

8

I never said that sanitizing data from the database was necessary - all I said was that any sanitizing of the data in connection with the database would only take place after reading the data from the database. You’d only do this if you were really paranoid about your database being tampered with. As sanitizing is an input process there is no other point in relation to the database processing where sanitizing could be applied. Unless you are paranoid about someone gaining access to your database and tampering with the data there then there is no reason for sanitizing data in connection with the database at all.

The other places where sanitizing can be and is much more likely to be applied have nothing whatever to do with the database. Probably the most likely place where it gets used is where there is system data being passed using GET or POST that is not coming from user input but where the user could potentially tamper with it and where therefore you don’t need to validate it as the user is not supposed to have touched it and so it ought to be valid but where you sanitize it so as to ensure that if a user did tamper with it that it still can’t do any significant harm. You’d do that long before any consideration of what is to be done with the data so whether or not the data is going to be written to a database wouldn’t come into it at all.

Where using prepare has made a difference to database processing is where you have valid data (already validated or sanitized) that could be misinterpreted as a database command. Provided that you use prepare to keep the SQL completely separate from the data there is no possibility of this happening and so no need to escape the valid data as used to be needed when the SQL and data were both included in the same string passed to the database for processing - the escaping was needed to prevent the valid data being misinterpreted as SQL and has nothing to do with sanitizing ( whichstrips out invalid characters).