How to Prevent Pdo from Interpreting a Question Mark as a Placeholder

How to prevent PDO from interpreting a question mark as a placeholder?

Use the function call form. According to the system catalogs, the hstore ? operator uses the exist function:

regress=# select oprname, oprcode from pg_operator where oprname = '?';
 oprname | oprcode 
---------+---------
 ?       | exist
(1 row)

so you can write:

SELECT * FROM tbl WHERE exist(hst,'foo');

(Personally I'm not a big fan of hstore's operator-centric design and documentation, I think it discards the useful self-documenting properties of a function based interface without any real benefit and I usually use its function calls rather than its operators. Just because you can define operators doesn't mean you should.)

PHP PDO escape question mark so it doesn't think it is a placeholder

PDO is not confused by the question mark inside the quotes. I just test this with PHP 5.5.15.

$sql = "SELECT CONCAT('path/to/page/?id=', id) AS link FROM foo WHERE name = ?;";
$stmt = $pdo->prepare($sql);
$stmt->bindValue(1, 'name');
$stmt->execute();
print_r($stmt->fetchAll());

It works fine, with no error about a wrong number of parameters. Your error is caused by the way you're binding parameters, not by the SQL syntax.

I suspect you haven't shown us the whole SQL query, because WHERE without FROM is a syntax error anyway. So you must have additional parameter placeholders that you haven't shown us. It would also be helpful if you show us the way you're binding parameters (or passing parameters to execute()).

Can you omit PDO prepare if there's no placeholder/dynamic data in a query?

Yes, because the use of prepared statements have 2 main causes:

1. Enhance running the same query with different parameters.
2. Prevent sql injection by separating sql code from the parameters.

Since you have no parameters that could be handled by a prepared statement (table names cannot be a parameter), you do not gain anything by pushing the query through as a prepared statement.

You still need to make sure that whatever is returned by $this->table will not cause any issues with the generated sql code.

Prepared statements: Using unnamed and unnumbered question mark style positional placeholders

Prepared statements are used to speed up the repeated execution of the same query with different arguments.
If your aim is to insert many rows at once it is better to execute regular insert query, which will be faster than the prepared insert. However, if you insisted on this solution, you could use arrays as arguments of prepared statement. Example:

create table foo(id int, val text);

prepare insert_into_foo (int[], text[]) as 
    insert into foo 
    select unnest(a), unnest(b)
    from (values ($1, $2)) v(a, b);

execute insert_into_foo (array[1,2,3], array['a','b','c']);
execute insert_into_foo (array[4,5,6,7], array['d','e','f','g']);

deallocate insert_into_foo; 

The main disadvantage of this trick is that you need to be very careful to put the same number of arguments in the arrays. Any mistake could be painful. Therefore I would suggest to use it with a fuse:

prepare insert_into_foo (int[], text[]) as 
    insert into foo 
    select unnest(a), unnest(b)
    from (values ($1, $2)) v(a, b)
    where array_length(a, 1) = array_length(b, 1); -- fuse

execute insert_into_foo (array[1,2], array['a','b','c']); -- does nothing

How does this PDO Code protect from SQL Injections?

(There's a bug in the 2nd line; the string isn't terminated. Add a "); to the end, and you should be ok. It's on the page you linked to as well, so its their fault. You of course also need to supply the values that'll substitute the question marks, and then actually run the query, before you get any results.)

Anyway, to the point. PDO looks for the ? or :name markers, and replaces them (in order or by name, respectively) with the values you specify. When the values are inserted into the query string, they're first processed to escape anything that could be used for injection attacks.

It's similar to using mysql_real_escape_string() (or the weaker addslashes()) on a value before using it in a query, but PDO does it automatically and is better at it.

Question mark placeholder

I really think you should use a library like PDO, but here is a solution nonetheless:

public function where($query)
{
    $args = func_get_args();
    array_shift($args);

    $query = preg_replace_callback('/\?/', function($match) use(&$args)
    {
        return array_shift($args); // wrap in quotes and sanitize
    }, $query);

    return $query;
}

PHP - PDO not taking imploded array as question mark parameter for IN clause in SELECT query

A ? placeholder can only substitute for a single literal. If you want an IN clause to accept an arbitrary number of values, you must prepare a new query for each possible length of your array.

E.g., if you want to select ids in array [1, 2], you need a query that looks like SELECT * FROM tbl WHERE id IN (?,?). If you then pass in a three-item array, you need to prepare a query like SELECT * FROM tbl WHERE id IN (?,?,?), and so on.

In other words, you cannot know with certainty what query you want to build/create until the moment you have the data you want to bind to the prepared statement.

This is not a PDO limitation, it is fundamental to how prepared queries work in SQL databases. Think about it--what datatype would the ? be in SQL-land if you said IN ? but had ? stand in for something non-scalar?

Some databases have array-types (such as PostgreSQL). Maybe they can interpret IN <array-type> the same way as IN (?,?,...) and this would work. But PDO has no way of sending or receiving array-type data (there is no PDO::PARAM_ARRAY), and since this is an uncommon and esoteric feature it's unlikely PDO ever will.

Note there is an extra layer of brokenness here. A normal database, when faced with the condition int_id = '1,2,3,4' would not match anything since '1,2,3,4' cannot be coerced to an integer. MySQL, however, will convert this to the integer 1! This is why your query:

$pstmt = $db->prepare('SELECT * FROM `oc_product` WHERE `product_id` IN (?)');
$pstmt->execute(array('28,29,30,46,47'));

Will match product_id = 28. Behold the insanity:

mysql> SELECT CAST('28,29,30,46,47' AS SIGNED INTEGER);
+------------------------------------------+
| CAST('28,29,30,46,47' AS SIGNED INTEGER) |
+------------------------------------------+
| 28                                       |
+------------------------------------------+
1 rows in set (0.02 sec)

---

Related Topics