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

Question

Bounty update: Already got a very good answer from Mark. Adapted := into :, below. However, I'm still looking for similar schemes besides DBIx. I'm just interested in being compatible to anything.

---

I need advise on the syntax I've picked for "extended" placeholders in parameterized SQL statements. Because building some constructs (IN clauses) was bugging me, I decided on a few syntax shortcuts that automatically expand into ordinary ? placeholders.
I like them. But I want to package it up for distribution, and am asking myself if they are easily understandable.

Basically my new placeholders are ?? and :? (enumerated params) and :& and :, and :| and :: (for named placeholders) with following use cases:

-> db("  SELECT * FROM all WHERE id IN (??)  ", [$a, $b, $c, $d, $e])

The ?? expands into ?,?,?,?,?,... depending on the number of $args to my db() func. This one is pretty clear, and its syntax is already sort of standardized. Perls DBIx::Simple uses it too. So I'm pretty certain this is an acceptable idea.

-> db("  SELECT :? FROM any WHERE id>0   ",  ["title", "frog", "id"]);
// Note: not actually parameterized attr, needs cleanup regex

Admit it. I just liked the smiley. Basically this :? placeholder expands an associative $args into plain column names. It throws away any $args values in fact. It's actually useful for INSERTs in conjunction with ??, and sometimes for IN clauses. But here I'm already wondering if this new syntax is sensible, or not just a misnomer because it mixes : and ? characters. But somehow it seems to match the syntax scheme well.

-> db("  UPDATE some SET :, WHERE :& AND (:|)   ", $row, $keys, $or);

Here the mnemonic :, expands into a list of name=:name pairs separated by , commas. Whereas the :& is a column=:column list joined by ANDs. For parity I've added :|. The :& has other use cases out of UPDATE commands, though.
But my question is not about the usefulness, but if :, and :& appear to be rememberable?

 -> db("  SELECT * FROM all WHERE name IN (::)  ", $assoc);

After some though I also added :: to interpolate a :named,:value,:list very much like ?? expands to ?,?,?. Similar use cases, and sensible to have for uniformness.

Anyway, has anybody else implemented a scheme like that? Different placeholders? Or which would you recommend for simplicity? Update: I know that the PHP Oracle OCI interface can also bind array parameters, but doesn't use specific placeholders for it. And I'm looking for comparable placeholder syntaxes.

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

If you are willing to spend some time to learn doctrine then you could do amazing stuff like:

$q = Doctrine_Query::create()
    ->select('u.id')
    ->from('User u')
    ->whereIn('u.id', array(1, 3, 4, 5));

echo $q->getSqlQuery();

what would produce a query like that:

SELECT 
u.id AS u__id 
FROM user u 
WHERE u.id IN (?, 
?, 
?, 
?)

This example was taken from:doctrine dql documentation