Slight extension for SQL prepared statements syntax. Need advice

Question

In my database abstraction library I am extending SQL prepared statements syntax to hint a parser with expected literal type. I take it as a very essential improvement, my reasoning you can read here. If you want to question the idea itself, please let me know in the comments - I'd like to start another question instead of mixing it here.

The advice I want is on the syntax. I am quite ignorant of all the languages and dialects beside php and mysql, and may be there already a general purpose solution I am unaware of. If not - then I want to know your personal opinion or suggestion.

At the moment, only positional placeholders supported. Yet I was asked many times to add support for the named ones. However, I cannot decide which syntax to choose for that.

At the moment I mark a conventional question mark with single letter, i.e. ?s - string, ?i - int, ?n - name (identifier) and so forth.

Yet for the named placeholder (:name style) the only sane option to me is

i:name

or, if I decide to add more verbose modifiers, even

int:name

so, the query would look like

SELECT * FROM ident:table WHERE id IN (int:id)

The question is: do you see any drawbacks for such a syntax? Do you have a suggestion for any other style? Do you think it's readable at all?

Another question is on repeating things. At the moment I have two placeholders for the arrays - ?a for the comma separated string list and ?u for the ?n=?s pairs. It bugs me a long time already, that such a syntax is superfluous, yet insufficient. What if I need a set of comma-separated identifiers? Or even a complex statement like whole VALUES clause? With some sort of repetition operator I can make SQL query building dramatically better. Here are examples of what I am thinking of:

SELECT * FROM table WHERE id IN([?i])
SELECT [?n] FROM table
INSERT INTO table VALUES [(?i,?s,?s,?s)]
UPDATE table SET [?n=?s]

As you can see, I use square brackets to designate a repetition. However, I am still not sure if I should use them. First objection is that they will collide with SQL server identifier syntax. Next is rather philosophical one - it is another syntax element and I want to introduce as less changes to the syntax as possible.

Also, this syntax will require much more complicated parsing logic, which I am trying to avoid too. So - another option is just to extend the set of types, either with distinct placeholders like ?k for the list of numbers, ?o for the list of identifiers, of with adding a single=letter modifier to the existing type like ?as (or ?astr or astr:name) is an array of strings, ?an and the like.

There is another option - to detect repetitions automatically. If we have a scalar - format it as a single value. If it's array coming - format as a comma-separated list. However, many people find this approach unacceptable, as it can lead to unpredictable results and erroneous (however still safe) query. I am still in doubts if I may choose this way.

Answer 1

The syntax has to cover three things:

1. Some way to specify the placeholder type
2. Some way to specify the placeholder source
3. Some way to handle arrays

You already have the type-specification covered with the ?type syntax - I'd keep this as is and combine it with elements to handle the other two.

The array handling using [...] (with ... being another placeholder-expression) also looks reasonable.

The key part is how you specify the placeholder source. It may not be immediately obvious looking at the code samples, but there are actually quite a large number of different placeholder sources. Lets go through some examples:

• SELECT xyz FROM table WHERE name = ?s AND age > ?i

  Assuming $params is the top-level set of parameters, then the ?s placeholder takes $params[0] and the ?i placeholder takes $params[1]. As such, the default-behavior you likely want is that the parameter source is an auto-incrementing zero-based key.

• INSERT INTO table VALUES [(?i,?s,?s,?s)]

  Here the array for [(?i, ?s, ?s, ?s)] is presumably in $params[0] and if iterated via foreach ($params[0] as $key => $value) then the inner placeholders will be populated from $value[0] to $value[3]. This is consistent with the previous example.

• SELECT * FROM table WHERE id IN([?i])

  Here we have a problem: Presumably the $params[0] array is supposed to be of the format [$id1, $id2, $id3, ...] rather than [[$id1], [$id2], [$id3], ...]. Thus in this case we want ?i to be $value itself, rather than $value[0] (as the auto-incrementing approach implies)

• UPDATE table SET [?n=?s]

  Here this problem becomes even more clear. In this case you likely want the array format to be [$name1 => $value1, $name2 => $value2, ...] rather than [[$name1, $value1], [$name2, $value2], ...]. Thus the ?n placeholder needs to bind to $key and ?s to $value.

• INSERT INTO table VALUES [(:id, :name)]

  Of course you can now do the same thing using named keys. Here :id binds $value['id'] and :name binds $value['name'].

• SELECT :cols FROM table WHERE id IN(:ids)

  And arrays might also be taken from named parameters. This will need to be handled in some way as well.

This is a rough overview of what you need to be able to handle to fully support arrays.

Here is a possible syntax which covers all possibilities. In the following I'll use $key and $value to denominate the current key/value pair. For the top-level $value will contain the top-level placeholders and $key is not defined.

?type      -- source is implicitly $value[$i] with $i being an auto-incrementing,
           -- zero-based integer. I.e. $value[0], $value[1], $value[2], ...
:name?type -- source is $value['name']
v?type     -- source is $value itself
k?type     -- source is $key itself

[...]      -- source for array is $value[$i], again with auto-incrementing $i
:name[...] -- source is $value['name']
v[...]     -- source is $value itself
k[....]    -- source is $key itself

This is how the examples would look like using this syntax:

• SELECT xyz FROM table WHERE name = ?s AND age > ?i (the same)
• INSERT INTO table VALUES [(?i,?s,?s,?s)] (the same)
• SELECT * FROM table WHERE id IN([v?i]) (need to specify that we want the value)
• UPDATE table SET [k?n = v?s] (need to specify that we want the key/value)
• INSERT INTO table VALUES [(:id?i, :name?s)] (added types)
• SELECT :cols[v?n] FROM table WHERE id IN(:ids[v?i]) (added array spec)

One thing that isn't covered by these examples is the v[...] syntax. One possible use case is to address all top-level placeholder values as an array:

query('INSERT INTO table VALUES (v[?s])', 'v1', 'v2', 'v3');
// vs
query('INSERT INTO table VALUES ([?s])', array('v1', 'v2', 'v3'));

The k[...] syntax is included for consistency only - it has no practical use and isn't even possible for arrays (only PHP >= 5.5 iterators can have arrays as keys).

Now, this syntax just provides you with all the elements you need to fully handle arrays - the details (like, used characters and order) can likely be improved upon.

At this point one may start wondering whether full array handling is really necessary - you need quite a lot of syntactical elements for it. Maybe a simple ?ai for IN clauses will do?

I think it may make sense to include this kind of syntax, despite the added complexity. It does not add syntactical overhead when not used (only the ?type part is required) and you have the functionality when you need it. In my experience SQL injection vulnerabilities (total noobs notwithstanding) are usually introduced during generation of complex queries, as query generation puts you outside the usual placeholder framework. The best way to prevent this is to make sure that you can even create more complex queries without leaving the framework.

Answer 2

I suggest making all parameters start with ?. The advantages of this are:

• It extends an existing familiar paradigm.
• There won't be any syntax clashes or ambiguities.
• There is a clear visual indicator of what things are placeholders.

So name identifiers would look like:

SELECT * FROM ?ident:table WHERE id IN (?int:id)

If you want to implement more complex placeholders, I suggest you start by defining a general construct such as ?{...}. Then you can use whatever is easiest and clearest within that, without fear of clashing with SQL. And, again, there is a very clear visual indicator in the query of what is SQL and what is a parameter.

insert into table values ?{array:(?i,?s,?s,?s)}
update table set ?{array:(foo=?i,bar=?s)}

The trick in this whole project is implementing the right level of complexity, so that it helps rather than making things more complicated. I think it is an idea with potential, though.