Prepared Statements and quotes

[Banana Man]

I have just started using PDO Prepared Statements and was wondering if i still need to escape quotes and double quotes when inserting data in my MySQL database? Everything seems to work fine if i dont escape them and just insert strings as is but im worried im not thinking about something that will bite me in the ass down the road?

Thanks!

[vectorialpx]

I have just started using PDO Prepared Statements and was wondering if i still need to escape quotes and double quotes when inserting data in my MySQL database?

Short answer: no. It's Ok, you're safe, if you are really using preparedStatements

Example:

<?php

$color = $_POST['color'];
$calories = $_POST['calories'];

// NOT OK, $color still may have injections
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :cal AND colour = "' . $color . '"
');
$sth->execute(array( ':cal' => $calories ));
$red = $sth->fetchAll();

// OK - you're safe
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :cal AND colour = :col
');
$sth->execute(array( ':cal' => $calories, ':col' => $color ));
$red = $sth->fetchAll();

?>

[cpradio]

I have just started using PDO Prepared Statements and was wondering if i still need to escape quotes and double quotes when inserting data in my MySQL database? Everything seems to work fine if i dont escape them and just insert strings as is but im worried im not thinking about something that will bite me in the ass down the road?

Thanks!

Short answer: no. It's Ok, you're safe, if you are really using preparedStatements

I'm in agreement with @vectorialpx ; however, see the emphasis I added. If you can show us the snippet of code in question, we can help ease your concerns or validate them.

But since prepared statements can be utilized in poorly executed ways, we really need to see if your snippet is similar to what vectorialpx shows, uses bindParam, or is concatenating the strings to the query when being passed to prepare.

[Banana Man]

Here is an example of a database query for my database:

// get the open day 
	$params = array('2', 'title');

	$stmt = $database->query("
				SELECT 
				open_day_output.open_day_output_EN_GB

				FROM
				open_day,
				open_day_output

				WHERE 
				open_day.open_day_id = open_day_output.FK_open_day_id AND
				open_day.FK_product_info_id = ? AND
				open_day_output.open_day_output_string_id = ?
				",

				$params
				);

// and then my database class is like this:
		
		// create the array to return for json
		$row = $database->fetch_pdo($stmt)

                        public function fetch_pdo($stmt) {
                        // set the PDO fetch mode
			$stmt->setFetchMode(PDO::FETCH_BOTH);
			
			$row = $stmt->fetch();

                       return $row;
}

[vectorialpx]

Looks OK.
However, I prefer the "associative way" like

<?php
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :cal AND colour = :col
');
?>

It's way visible and when you'll have an SQL with 50-100 lines, it will help you a lot.

[cpradio]

@Banana Man ; are you using a framework, as from the manual, I don't see where query() in PDO accepts parameters to use for the query... however, that is the only question I have in regards to the sample you posted.

[Banana Man]

Thanks! That puts my mind at rest.

I'm not using a framework. I have a query method in my database class that i am calling. The first attribute i send is the sql query and the second is an array with my bind parameters.

// query the database
	public function query($sql= NULL, $params = NULL) {
	
		try {
			$stmt = $this->pdo->prepare($sql);
			
			// execute the query
			$stmt->execute($params);
			
			return $stmt;
			
			// close the database connection
			$stmt = NULL;
			
		} // close try

[cpradio]

Thanks! That puts my mind at rest.

I'm not using a framework. I have a query method in my database class that i am calling. The first attribute i send is the sql query and the second is an array with my bind parameters.

// query the database
	public function query($sql= NULL, $params = NULL) {
	
		try {
			$stmt = $this->pdo->prepare($sql);
			
			// execute the query
			$stmt->execute($params);
			
			return $stmt;
			
			// close the database connection
			$stmt = NULL;
			
		} // close try

Perfect, that is what I was hoping to see behind that method You are on the right path.