Prepared Parameterized Query with Pdo

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'];
}

PDO prepared statement bind parameters once for different queries

If I have multiple queries where the parameters are exactly the same, do I need to bind the same parameters again using

Yes, of course.

Parameters are bound to each query, not to PDO or a database globally.

On a side note, with PDO you don't have to bind variables explicitly, so there is a solution to your "problem": just don't bind at all but send your data directly into execute() as it shown in the Dharman's excellent 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.

SELECT within SELECT PDO prepared statement

To clear any confusion, what i'm doing is this:

$pdo = new PDO('..');
$sql = 'SELECT id FROM users WHERE username = :username';
$statement = $pdo->prepare($sql);
$statement->bindParam(':username', $_POST['username']);

Question is, what if $_POST['username'] contains 'SELECT * FROM users' (or any other query) ?

This query would return the ids of all users with the username "SELECT * FROM users".

By passing $_POST['username'] as parameter the database knows that whatever string $_POST['username'] may contain it is NOT part of the query. It's just a string.

This prevents SQL injection since the parameter will NOT be executed. This also means that

SELECT name, continent FROM world
WHERE continent IN
(SELECT continent FROM world WHERE name='Brazil')

the second select acting as the user input parameter - so $_POST['name'] contains this query SELECT continent FROM world WHERE name='Brazil'

won't work. Because you can't include queries in parameters. Well you can but they will not be executed.

PHP PDO Prepared Statement parameter causing error

Your $limit parameter is being escaped as one parameter, where it should be escaped as two. Your sql will currently look something like "limit '0, 8';" where it should look like "limit 0, 8";

To solve this, you should split your limit parameter into two. Edit the end of your SQL to look like:

LIMIT :offset, :limit

And your parameter list to look like:

$query_params = array (
    ':offset' => ($pagenum - 1) * $page_rows,
    ':limit' => $page_rows
);

As Mr Smith mentioned, you'll also have to add the line:

$db->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );

To ensure the limit parameters are correctly handled as integers rather than strings.

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}%"]);

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!

Is there an advantage to using prepared statements in PDO when there are no variables going into the sql query

No, there is no benefit. Although the difference is negligible, you can always write a function to implement even such a minor optimization:

class MyPDO extends PDO
{
    public function run($sql, $args = NULL)
    {
        if (!$args)
        {
            return $this->query($sql);
        }
        $stmt = $this->prepare($sql);
        $stmt->execute($args);
        return $stmt;
    }
}

now you've got PDO that can decide whether to run a prepared statement or not:

$pdo   = new MyPDO(...);
$count = $pdo->run("SELECT count(*) FROM t")->fetchColumn(); // no prepare
$user  = $pdo->run("SELECT * FROM t WHERE id=?",[$id])->fetch(); // prepare

---

Related Topics