pdo and sql injections

[Dragen]

Am I right in thinking that data input into a mysql database using pdo is safe from sql injections?

I read on the pdo page:

The user input is automatically quoted, so there is no risk of a SQL injection attack.

 

Is this always the case with pdo, or is it just for certain methods of database input?

for example, if I had my query as this:

$dbh->query("INSERT INTO `table` ('var') VALUES ($variable)"

would I need to do anything to safeguard from sql injections?

[trq]

Is this always the case with pdo, or is it just for certain methods of database input?

 

It is only true for prepared statements.

[Dragen]

Thank you Thorpe. Is there a similar function to mysql_real_escape_string with pdo then?

Or do I just need to rely on security checks beforehand?

[trq]

Why not use prepared statements?

[Dragen]

Good point. So is this a good way of doing it?

		try{
		$stmt = $dbh->prepare("SELECT `name` FROM `users` WHERE `id` = '?' LIMIT 1");
			if($stmt->execute(array($id))){
				while($row = $stmt->fetch()){
					return $row['name'];
				}
			}else{
				return false;
			}
	}catch(PDOException $e){
	   return false;
	}		

I did try without the try/catch part, but on an sql error it throws up a whole error, whereas I was hoping the if/else statement would stop that. Reading up on pdo though it seems that it throws out all errors as a safety feature.

[aschk]

Exceptions are becoming more prevalent in PHP now, and my suggestion is to use them as they can be handy

In your catch statement you can use

...} catch (PDOException $e) {
  // Log error to log file using $e->getMessage();
  // or mail me the SQL statement + pdo error message.
}

 

Unfortunately there's not a lot you can do stop PDO throwing an exception (unless you extend it and override the function, but we're not going there).

[Dragen]

Thanks aschk, that's pretty much what I'm doing.

I just though I could do it without the try/catch statement, but without it the exception is output to the user, which makes sense.

[Daniel0]

Good point. So is this a good way of doing it?

 

Yes, except it should be

$stmt = $dbh->prepare("SELECT `name` FROM `users` WHERE `id` = ? LIMIT 1");

(without the single quotes around the question mark) as PDO will handle that for you. Otherwise you might end up with two single quotes.

[Dragen]

brilliant! thanks for all the advice guys!