6

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.

  • PDO already supports named parameters. At the very least, consider following their lead. Doctrine 2's DBAL connection object extends PDO and handles parameter arrays. – Cerad Mar 18 '14 at 13:54
  • 1
    PDO doesn't support type-hinted parameters, which is the question. Say, there is no placeholder in pdo to bind an array for the IN() mysql statement – Your Common Sense Mar 18 '14 at 13:55
  • True enough. So I guess I would question the need for type hints. In any event, I would not use any special characters to indicate repetition. Just passing an array seems enough. – Cerad Mar 18 '14 at 14:11
  • 1
    Read through your link. Part 7 where you make the case for specifying format types is just plain wrong. PHP/PDO is smart enough to know the difference between integers and strings. Your limit example will work fine. You very seldom need to specify data types. I never do. Things just work. – Cerad Mar 18 '14 at 14:15
  • 1
    You need to check your premises. 1. It won't work if variable is bound with string type (passed to execute(), for example). 2. It won't work if variable type is string even if you set PARAM_INT explicitly. In fact, PHP/PDO is NOT smart enough to know the difference between integers and strings. Even with conventional types. – Your Common Sense Mar 18 '14 at 15:37
3
+50

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.

  • Yes, that's my goal exactly - to reduce the cases where we can leave framework. Or, in other words - to make "use prepared statements!" imperative more usable in the real world. – Your Common Sense Mar 26 '14 at 15:35
  • In fact, even ?type isn't required! For compatibility and readability I am going to keep ? and :name as synonyms for ?s and :name?s respectively. Gonna be great help for everyone, especially when (if) I'll be able to transfer the approach to PDO. though it will require much more compatibility tuning, including inability to use []. I am inclined to simple one-letter modifier anyway. At least for the first release. Your answer is as great as usual :) I am still pondering on it. – Your Common Sense Mar 26 '14 at 15:45
  • 2
    After pondering some time on the source problem, I decided that having each placeholder type to serve a sole purpose, I'll be able to list the source in documentation. Which will keep syntax less complex, which is still my concern. So, for the moment I am thinking of the less complex (and less ambiguous as a result) syntax like (\:[0-9a-z_])\?(t|y|p|e|s), marking arrays with single letter. Sorry for not taking your whole suggestion. I am just afraid of global changes which consequences I cannot foresee. The more syntax freedom - the more tests. So, I'd like it as concise as possible. – Your Common Sense Mar 30 '14 at 3:34
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.

  • Thank you for this. It makes some sense and I am considering this variant too. It only bothers me that we have one superfluous character. But may be it is not that big deal - anyway, most of placeholders will remain regular ?s and :name-s - as we need extra types relatively seldom. – Your Common Sense Mar 25 '14 at 10:04
  • Character counting is what leads to lack of clarity. It is much more important for it to be readable and logical. – user82096 Mar 25 '14 at 12:47
  • Everybody appears to be forgetting the error handling here. What happens if the passed data is not an "int". Crash out, throw an exception, return an SQL error. What happens of you passed three int, how would you know which one was wrong. Upfront validation would seem to be the answer, in which case all of this extra complexity in unnecessary. – James Anderson Mar 26 '14 at 1:28
  • @JamesAnderson, what is wrong with throwing an exception or returning an SQL error? The point is preventing malicious behavior. – user82096 Mar 26 '14 at 7:03
  • @dan1111 -- but using ? prevents sql injection anyway. – James Anderson Mar 26 '14 at 10:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.