PHP Pdo Prepared Statements

PHP PDO prepared statements

There are two great examples on the pdo::prepare() documentation.

I have included them here and simplified them a bit.

This one uses ? parameters. $dbh is basically a PDO object. And what you are doing is putting the values 150 and 'red' into the first and second question mark respectively.

/* Execute a prepared statement by passing an array of values */
$sth = $dbh->prepare('SELECT name, colour, calories
                      FROM fruit
                      WHERE calories < ? AND colour = ?');

$sth->execute(array(150, 'red'));

$red = $sth->fetchAll();

This one uses named parameters and is a bit more complex.

/* Execute a prepared statement by passing an array of values */
$sql = 'SELECT name, colour, calories
        FROM fruit
        WHERE calories < :calories AND colour = :colour';

$sth = $dbh->prepare($sql);
$sth->execute(array(':calories' => 150, ':colour' => 'red'));

$red = $sth->fetchAll();

MySQL PDO Prepared Statements

You need to pass parameter 2 by reference to the bindParam function.

Replace this-

$statement->bindParam("dir", "test");

with this-

$test = "test";
$statement->bindParam(":dir", $test);

Check out the doc

variable: Name of the PHP variable to bind to the SQL statement parameter.

PDO prepare statement and match against in boolean mode

You should simply create a statement like this:

$query = 'SELECT COUNT(*) AS total FROM articles WHERE MATCH(title) AGAINST (:query IN BOOLEAN MODE)';
$stmt = $pdo->prepare($query);

And then bind the params:

$keywords = ['php', 'mysql'];
$against = '';
for($i = 0; $i < count($keywords); $i++){
    $against .= ' +' . $keywords[$i];   
}
$stmt->execute(array(
    ':query' => $against
));

How does the PDO prepared statements works “inside”?

Exact behaviour depends. For instance, the MySQL driver in PDO can do two entirely different things depending of the value of the PDO::ATTR_EMULATE_PREPARES attribute:

Enables or disables emulation of prepared statements. Some drivers do
not support native prepared statements or have limited support for
them. Use this setting to force PDO to either always emulate prepared
statements (if TRUE), or to try to use native prepared statements (if
FALSE). It will always fall back to emulating the prepared statement
if the driver cannot successfully prepare the current query.

Emulated mode is just like you describe: PHP has a SQL parser that replaces place-holders with actual values. The only actual benefit is that code is cleaner and easier to maintain.

Native mode basically sends code and data to the server in two separate channels. Data can be sent as-is (even in binary mode). This requires both client and server support. Benefits include security, bandwidth saving and the possibility to parse SQL code once and run it several times with different data sets. Actual implementation depends on DBMS.

Is PDO still emulating prepared statements for MySQL?

The answer you are referring to is more like a scary tale than a real help. If you read the fine print at the bottom, it says that with actual software versions you are all right (actual means released past 2010).

So you can tell that security-wise there is no difference whether prepared statements are emulated or not. Hence, the answer to your question is not that important.

Besides, you incorrectly understood a certain statement from it.

However, be aware that PDO will silently fallback to emulating statements that MySQL can't prepare natively

It doesn't mean then mysql doesn't support native prepared statements at all. It means that only for some certain kinds of queries mysql does not support prepared statements. For such queries you don't have too much a choice, so it doesn't really matter again.

To make it clear

PDO does still emulate prepared statements for MySQL by default, when no option is set.
For the most used query types such as SELECT, INSERT, UPDATE and such, PDO does not emulate prepared statements for MySQL if explicitly told to use native statements. by the way, the list of supported statements is quite inclusive
as this behavior is decided on the server side, changing PHP API from PDO to mysqli won't help.
for some rarely used query types it may silently fallback to emulating statements but it is neither a security concern nor you have a choice anyway.

To sum it up:

For convenience sake, disable the emulation as a connection option. Means you have to change your current single-line connection to a full-blown PDO connection script which I suggest as a canonical example and then just move on.

PDO Prepared Statements with % and concat

The thing you need to understand is that a placeholder have to represent a complete data literal only.

And thus your code will be:

Scenario 1

Create a search string with wildcard characters in PHP,

$type = "$type%";

and then you can bind it the usual way.

Scenario 2

Just add one more argument for CONCAT():

concat(uuid(),'-', :id)

PDO prepared statements. Select everything

execute() runs a prepared statement which allows you to bind parameters to avoid the need to escape or quote the parameters. execute will also perform better if you are repeating a query multiple times.

The query() runs a standard SQL statement and requires you to properly escape all data to avoid SQL Injections and other issues.

so in your case you have nothigs to escape .. because all the code for sql i based on literal and you don't need prepare()

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

PHP Pdo Prepared Statements