How to escape during PDO query statement in php?

Question

(Edit:Guys, Before jumping to any conclusions, I'm asking how do you escape a query variable from the Example#2 from php.net website. I tried lot of ways but they all gave me errors. If you can please read that Example and post your version of that exact Example#2. Also please read about why they have that example there.)

I was searching for a reliable 'row:count' method to use with PHP PDO across multiple database types, and came across below code from php.net http://php.net/manual/en/pdostatement.rowcount.php (See Example:#2) It says to do a row count to see if an entry exists in a database using a SELECT statement, the error proof method is to use PDO::query() instead of PDOStatement::fetchColumn().

My question is I know how to bind and execute with PDO, but I don't know how to assign a user submitted variable($username) to this $sql statement and escape it successfully?

Is it possible to bind parameters to this $sql mehod using PDO?

            try{
            $conn = new PDO($dsn, $db_username, $db_password);
            $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
            $this->db = $conn;
        } catch(PDOException $e){
            echo 'Error:'.$e;
        }

    public function usernameExists($username){

        //Check db for a match.
        $sql = "SELECT * FROM users WHERE username = '".$username."'";
        $results = $this->db->query($sql);

        if($results->fetchColumn() > 0){
            //Matching username found in the db
            return true;
        }else{
            //No matching username in db
            return false;
        }

}

Use prepared statements. Change `'$username'` to a question mark, pass the value in an `array` in the `execute`. Example #3, http://php.net/manual/en/pdo.prepared-statements.php. — chris85, Aug 24 '15 at 19:27
read this tutorial http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers — cmorrissey, Aug 24 '15 at 19:27

kittykittybangbang · Answer 1 · 2015-08-24T19:59:49.983

1

You're looking for bindValue. With it, you can use a placeholder when writing your query, then pass the user's input afterward.

For example:

public function usernameExists($username){
//$result = $this->db->query('SELECT * FROM users WHERE username = ');
    //Check db for a match.
    $sql = "SELECT * FROM users WHERE username = :username";
    $s = $conn->prepare($sql);
    $s->bindValue(':username',$username);
    $s->execute();
    $results = $s->fetchAll();

    if(count($results) > 0){
        //Matching username found in the db
        return true;
    }else{
        //No matching username in db
        return false;
    }

For more info, see the PHP manual.

edited Aug 24 '15 at 19:59

answered Aug 24 '15 at 19:29

kittykittybangbang

2,380
4
16
27

Thank you for the reply. I tried this before. Gave me this error. Warning: PDO::query() expects parameter 1 to be string, object given in line 66( $results = $this->db->query($s);). And This:Fatal error: Call to a member function fetchColumn() on a non-object in line 68($results->fetchColumn() > 0). – ToxicCyberBear Aug 24 '15 at 19:47
Honestly, I don't usually run my queries this way, so can't speak authoritatively to that error. I normally use `$s->execute();`, then `$result = $s->fetchAll();`. Edited to incorporate this method instead -- let me know if you still run into an error. – kittykittybangbang Aug 24 '15 at 19:58
Thank you for the reply. I found a way around this. – ToxicCyberBear Aug 24 '15 at 20:02

score 0 · Accepted Answer · answered Aug 24 '15 at 19:28

You're going to want to use a parameterized query like this:

<?php
$value = "whatever";

$stmt = $dbh->prepare("SELECT * FROM TABLE_NAME where column_name = ?");
if ($stmt->execute(array($value))) {
  while ($row = $stmt->fetch()) {
    print_r($row);
  }
}
?>

mario · Answer 3 · 2015-08-24T19:42:23.843

If you really wanted to quote+escape the string, then that's possible too. It even looks somewhat more legible with complex variable interpolation than your original string patching:

 $sql = "SELECT * FROM users WHERE username = {$this->db->quote($username)}";
                                      // ->quote itself adds ↑ 'quotes' around

Now of course: don't do that. Use a placeholder, and pass it per ->execute([$var]); instead. Strive for consistency.

How to escape during PDO query statement in php?

3 Answers3