How to Properly Use a Pdo Object For a Parameterized Select Query

How can I properly use a PDO object for a parameterized SELECT query

You select data like this:

$db = new PDO("...");
$statement = $db->prepare("select id from some_table where name = :name");
$statement->execute(array(':name' => "Jimbo"));
$row = $statement->fetch(); // Use fetchAll() if you want all results, or just iterate over the statement, since it implements Iterator

You insert in the same way:

$statement = $db->prepare("insert into some_other_table (some_id) values (:some_id)");
$statement->execute(array(':some_id' => $row['id']));

I recommend that you configure PDO to throw exceptions upon error. You would then get a PDOException if any of the queries fail - No need to check explicitly. To turn on exceptions, call this just after you've created the $db object:

$db = new PDO("...");
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

How do I create a PDO parameterized query with a LIKE statement?

Figured it out right after I posted:

$query = $database->prepare('SELECT * FROM table WHERE column LIKE ?');
$query->execute(array('value%'));

while ($results = $query->fetch())
{
    echo $results['column'];
}

using PDO object for a Select query

db_connect

class DB_Connect {
    public $dbh;
    // constructor
    function __construct() {

    }

    // destructor
    function __destruct() {
        // $this->close();
    }

    // Connecting to database
    public function connect() {
        require_once 'include/config.php';
        try {
            $hostname = DB_HOST ;
            $dbname   = DB_DATABASE;
            $this->dbh = new PDO("mysql:host=$hostname;dbname=$dbname", DB_USER, DB_PASSWORD);
        }
        catch(PDOException $e)
        {
            echo $e->getMessage();
        }
    }
}

db_functions

class DB_Functions {

    private $db;

    //put your code here
    // constructor
    function __construct() {
        require_once 'DB_Connect.php';
        // connecting to database
        $this->db = new DB_Connect();
        $this->db->connect();
    }

    // destructor
    function __destruct() {

    }


    function getUsers(){

        $sql = "SELECT * FROM users";
        foreach ($this->db->dbh->query($sql) as $row)
        {
            echo $row->id;
        }

        /*** close the database connection ***/
      //  $db = null;

    }
}

You have no database connection because you're now assigning your PDO connection to a variable. So you're connection is not accessible to the rest of your script. At least, that's what I'm thinking at the moment.

How to properly fetch object using PDO and php

As you guys said, i wasn't creating a PDO connection instead i was using mysqli. Sorry for the mistake, i've created the PDO connection successfully and rewrittten the code. it's working fine now

Solution:

db.php

          <?php
          $host   = 'localhost';
          $dbname = 'name';
          $username = "root";
          $password = "";
       $conn = new PDO('mysql:host=localhost;dbname=name', $username, 
       $password);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        ?>

and then replaced the following lines:

result.php

       if (isset($_GET['search'])){

       $search_query = $_GET['user_query'];

      $stmt = $conn->prepare("SELECT * FROM `food` 
       WHERE `food_keywords` LIKE :search ");

       $search_query = "%".$search_query."%";

      $stmt->bindParam(':search',$search_query, PDO::PARAM_STR);


       $stmt->execute();


    //fetch results set as object and output HTML
    while($obj = $stmt->fetch())
    {
        //rest goes here

      }
          }
             ?>

Understanding PDO Prepared Statements and Binding Parameters

You're correct that the first case is insecure. It's important to understand though, that preparing a statement only has value if you are using variable data, and/or executing the same query repeatedly. If you are executing plain statements with no variables, you could simply do this:

$sql = "SELECT * from myTable WHERE this_column IS NOT NULL";
$result = $conn->query($sql);

And end up with a PDOStatement object to work with, just like when you use PDO::exec().

For your second case, again, you're largely correct. What's happening is the variable passed to the database is escaped and quoted (unless you specify otherwise with the third argument to PDOStatement::bindParam(), it's sent as a string which is fine for most cases.) So, the query won't "fail" if bad data is sent. It behaves exactly as if you had passed a valid number that didn't exist as an ID in the database. There are, of course, some edge cases where you are still vulnerable even with a correctly prepared statement.

Also, to make life easier, you can use prepared statements like this, to do implicit binding:

$sql = "SELECT * FROM myTable WHERE id = :id";
$stmt = $conn->prepare($sql);
$stmt->execute([":id"=>$id]);

Or even like this, with un-named parameters:

$sql = "SELECT * FROM myTable WHERE id = ?";
$stmt = $conn->prepare($sql);
$stmt->execute([$id]);

Naturally, most of this has been explained in the comments while I was typing up the answer!

Getting raw SQL query string from PDO prepared statements

I assume you mean that you want the final SQL query, with parameter values interpolated into it. I understand that this would be useful for debugging, but it is not the way prepared statements work. Parameters are not combined with a prepared statement on the client-side, so PDO should never have access to the query string combined with its parameters.

The SQL statement is sent to the database server when you do prepare(), and the parameters are sent separately when you do execute(). MySQL's general query log does show the final SQL with values interpolated after you execute(). Below is an excerpt from my general query log. I ran the queries from the mysql CLI, not from PDO, but the principle is the same.

081016 16:51:28 2 Query       prepare s1 from 'select * from foo where i = ?'
                2 Prepare     [2] select * from foo where i = ?
081016 16:51:39 2 Query       set @a =1
081016 16:51:47 2 Query       execute s1 using @a
                2 Execute     [2] select * from foo where i = 1

You can also get what you want if you set the PDO attribute PDO::ATTR_EMULATE_PREPARES. In this mode, PDO interpolate parameters into the SQL query and sends the whole query when you execute(). This is not a true prepared query. You will circumvent the benefits of prepared queries by interpolating variables into the SQL string before execute().

---

Re comment from @afilina:

No, the textual SQL query is not combined with the parameters during execution. So there's nothing for PDO to show you.

Internally, if you use PDO::ATTR_EMULATE_PREPARES, PDO makes a copy of the SQL query and interpolates parameter values into it before doing the prepare and execute. But PDO does not expose this modified SQL query.

The PDOStatement object has a property $queryString, but this is set only in the constructor for the PDOStatement, and it's not updated when the query is rewritten with parameters.

It would be a reasonable feature request for PDO to ask them to expose the rewritten query. But even that wouldn't give you the "complete" query unless you use PDO::ATTR_EMULATE_PREPARES.

This is why I show the workaround above of using the MySQL server's general query log, because in this case even a prepared query with parameter placeholders is rewritten on the server, with parameter values backfilled into the query string. But this is only done during logging, not during query execution.

PDO Parameterized SELECT statement with looped conditional

Figured it out, the key was the FIND_IN_SET function since it's a short list of types:

$type_sql = ($_GET['extensions'])? "AND FIND_IN_SET(`type`, :type)" : "";
$sth = $dbh->prepare("SELECT * FROM `media` WHERE `active` = '1' AND `thumb` IS NULL $type_sql ORDER BY `created` DESC ");
$sth->bindParam(':type', $_GET['extensions']);
$sth->execute();
while($media = $sth->fetch()){
    // Do stuff
}

Using A PDO Prepared Statement On A Search Form Query - PHP

Alternative 1:

$sql = "SELECT imgp.*, u.id, u.firstname, u.lastname
  FROM imageposts AS imgp
  INNER JOIN users AS u ON imgp.user_id = u.id 
  WHERE image_title LIKE CONCAT('%', ?, '%')";
$stmt = $connection->prepare($sql);
$stmt->execute([$searchQuery]);

Alternative 2:

$sql = "SELECT imgp.*, u.id, u.firstname, u.lastname
  FROM imageposts AS imgp
  INNER JOIN users AS u ON imgp.user_id = u.id 
  WHERE image_title LIKE ?";
$stmt = $connection->prepare($sql);
$stmt->execute(["%{$searchQuery}%"]);

Can PHP PDO Statements accept the table or column name as parameter?

Table and Column names CANNOT be replaced by parameters in PDO.

In that case you will simply want to filter and sanitize the data manually. One way to do this is to pass in shorthand parameters to the function that will execute the query dynamically and then use a switch() statement to create a white list of valid values to be used for the table name or column name. That way no user input ever goes directly into the query. So for example:

function buildQuery( $get_var ) 
{
    switch($get_var)
    {
        case 1:
            $tbl = 'users';
            break;
    }

    $sql = "SELECT * FROM $tbl";
}

By leaving no default case or using a default case that returns an error message you ensure that only values that you want used get used.

PDO with parameter

Given

$this_var = 'apple';
$statement->prepare("SELECT * table WHERE $this_var = 1");

the query would be SELECT * table WHERE apple = 1;

---

Related Topics