Extended placeholders for SQL, e.g. WHERE id IN (??) Extended placeholders for SQL, e.g. WHERE id IN (??) sql sql

Extended placeholders for SQL, e.g. WHERE id IN (??)

I like the basic idea behind your proposal, but dislike the "naming" of the placeholders. I basically have two objections:

  • Your placeholders start either with : or with ?. You should choose one form, so a placeholder may be immediately recognized. I would choose ? because it has less possible collisions with SQL and is more common for denoting placeholders.
  • The placeholders are hard to understand and hard to remember. :& and :| seem plausible to me, but distinguishing ??, :?and : is quite hard.

I changed my DB class to support some more placeholders and be more intelligent: DB_intelligent.php (the part of the README about placeholders doesn't apply to this class. It is only for the normal class.)

The DB class has two kinds of placeholders: The multifunctional ? placeholder and the associative array placeholder ?x (x may be ,, & or |).

? placeholder: This placeholder determines the type of insertion from the type of the argument:

null                => 'NULL''string'            => 'string'array('foo', 'bar') => ('foo','bar')

?x placeholder: Every element in the array is converted to a `field`='value' structure and imploded with a delimiter. The delimiter is specified by the x component: , delimits by comma, & by AND and | by OR.

Example code:

DB::x(    'UPDATE table SET ?, WHERE value IN ? AND ?&',    array('foo' => 'bar'),    array('foo', 'bar'),    array('hallo' => 'world', 'hi' => 'back'));// Results in this query:// UPDATE table SET `foo`='bar' WHERE value IN ('foo','bar') AND `hallo`='world' AND `hi`='back'

Some thoughts I had while designing this version of the DB class:

An obvious thought that may arise: Why not use ? for all types of data, even associative arrays. Only add ?& and ?| additionally. Using ? on an associative array would be same as using ?, in the current design. The reason why I did not do this is security. You often want to insert data from a <select multiple> into the query (IN ?). But as HTML allows arraying (form[array]) form controls also an associative array with the same name may be submitted. Thus my Query Compositor would recognize it as a field => value list. Even though this probably would not harm security it would result in a SQL error which is bad.

You might want to avoid using := as a placeholder because it already has a usage in for example MySQL.

See for example this answer for a real world usage.

Very neat! I think the placeholders are fine as long as you document them well and provide plenty of examples when you distribute it. It's ok that you invented your own placeholders; someone had to think of using ?, after all.