SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Prepared Statements and quotes

    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!

  2. #2
    SitePoint Addict bronze trophy vectorialpx's Avatar
    Join Date
    Dec 2012
    Location
    Bucharest
    Posts
    247
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Banana Man View Post
    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 Code:
    <?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();

    ?>

  3. #3
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,071
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Banana Man View Post
    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!
    Quote Originally Posted by vectorialpx View Post
    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.
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  4. #4
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Here is an example of a database query for my database:

    Code:
    // 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;
    }

  5. #5
    SitePoint Addict bronze trophy vectorialpx's Avatar
    Join Date
    Dec 2012
    Location
    Bucharest
    Posts
    247
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)
    Looks OK.
    However, I prefer the "associative way" like
    PHP Code:
    <?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.

  6. #6
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,071
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    @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.
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  7. #7
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

    Code:
    // 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

  8. #8
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,071
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Banana Man View Post
    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.

    Code:
    // 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.
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •