SQLite Forum

Order of parameter substitution
Login

Order of parameter substitution

(1) By anonymous on 2021-01-11 21:51:31 [link] [source]

The documentation:

In the SQL statement text input to sqlite3_prepare_v2() and its variants, literals may be replaced by a parameter that matches one of following templates:

?
?NNN
:VVV
@VVV
$VVV

  1. Why are there alternative ways of specifying parameter placeholders?

  2. What is the order of substitution? Ordinal - that is, the order in which the parameters appear in the SQL statement?

  3. What are the guidelines for using each type of parameter placeholder?

  4. Can I simply use ? as the placeholder and use sqlite3_bind_text throughout (the prepared SQL statement is a literal, after all).

  5. Alternatively, (because there may be issues with numeric values being wrapped in quotes with 4.) substitute the parameters sequentially using sqlite3_bind_* where * is determined by typeof(argument)?

(2.1) By Keith Medcalf (kmedcalf) on 2021-01-12 08:09:17 edited from 2.0 in reply to 1 [link] [source]

Why are there alternative ways of specifying parameter placeholders?

Because you may access the parameters differently. All parameters are "numbered" parameters which may optionally have a parameter "name" associated with them to allow you to find the "parameter number" by "looking up the name.

? - means to use the next available parameter number that is one greater than any parameter number previously used starting at 1 if this is the first one.

?NNN - means parameter at location number NNN

:VVV, @VVV and $VVV are exactly the same as using the ? form except that the specified name is associated with the numbered parameter so that you can look up the parameter number by name. If the name has already been used then it is the equivalent of using ?NNN where the NNN is the number of the parameter that already has that name associated with it. The choice of the prefix symbol is for compatibility with the schemes used by a myriad of other SQL based products which use a various selection of leading characters or the symbols used by various host languages to refer to host variables in that language.

What is the order of substitution? Ordinal - that is, the order in which the parameters appear in the SQL statement?

There is no "order" of substitution. All parameters are substituted before the statement is executed.

If you mean how are "parameter slot numbers" allocated, they are allocated sequentially from left to right, The first (leftmost) anonymous (?) or named parameter is assigned to slot 1, then each parameter encountered after that proceeding from left to right is assigned to the slot one greater that the maximum slot number used used by any parameter to its left.

This means that for the statement SELECT ?47, ?3, ? the three parameters are assigned slot numbers 47, 3, 48 proceeding in order from left to right. Slots 1 and 2; and, 4 though 46 are unused. Slots numbered higher than 48 do not exist.

It does not matter the "order" you provide the parameters using sqlite3_bind*. The bindings are performed before the statement is executed by sqlite3_step. If one of the used paramaters is not provided a value, then its value will be null. You may provide values for slots 1 though 48 prior to executing the statement with sqlite3_exec however providing a value for slot 1 will have no effect since that slot is not used in the statement.

Can I simply use ? as the placeholder and use sqlite3_bind_text throughout

If you wish.

(the prepared SQL statement is a literal, after all)

This is irrelevant. The purpose of the various sqlite3_bind* API calls is so that YOU can store the parameter in different formats. For example, if you wish to pass the "integer" in i as a parameter then you would use the sqlite3_bind_int call to bind YOUR INTEGER i. Nothing prevents you from going though all the complication of converting the "integer i" into a text string using, for example, snprintf, and then using sqlite3_bind_text to bind the output of that conversion -- but why on earth would you want to do that?

Alternatively, (because there may be issues with numeric values being wrapped in quotes with 4.) substitute the parameters sequentially using sqlite3_bind_* where * is determined by typeof(argument)?

Data passed is data passed. It does not contain "wrapped in quotes" unless the data contains quotes. The type used in sqlite3_bind API is determined by the data type of the data item that YOUR APPLICATION is sending to SQLite3. The "typeof" the destination is mostly irrelevant.

(4) By anonymous on 2021-01-12 11:20:51 in reply to 2.1 [link] [source]

Thank you for providing this clear explanation (a much needed - at least for me - supplement to the existing online documentation).

However, this has raised a number of questions that I need to clarify for myself using this code flow:

sqlite3_prepare_v2(...)
 if (0!= sqlite3_bind_parameter_count(...)
    { 
      /* replace parameters here
       QUESTIONS: 1. Is the second argument of the sqlite3_bind_* APIs always 1 to parameter_count?
                  2. Or can it be the NNN following ? (or : or @ or $) If yes, how do I get the NNN?
     */ 
   }          
sqlite3_step(...)     
sqlite3_finalize(...)

(5) By Gunter Hick (gunter_hick) on 2021-01-12 11:55:18 in reply to 4 [link] [source]

You wrote the query, so you should know which/how many parameters you used and what to bind to each one.

If the parameter has a name (i.e. you did not use just plain ?), then you can map the index to a name and vice versa, but you cannot find out if an unnamed parameter with an index less than sqlite3_bind_parameter_count() is actually used.

(6) By Keith Medcalf (kmedcalf) on 2021-01-12 12:20:01 in reply to 4 [link] [source]

Is the second argument of the sqlite3_bind_* APIs always 1 to parameter_count?

Yes. The second argument is the parameter number and must be between 1 and the parameter count.

Or can it be the NNN following ? (or : or @ or $) If yes, how do I get the NNN?

Yes, the NNN in the ?NNN form is the parameter number.

You can think of the "parameters" as being an array with an index between 1 and the number returned by sqlite3_bind_parameter_count. So if the returned count is 7, then the valid parameter array index numbers are 1, 2, 3, 4, 5, 6; and, 7. The indexes into the parameter array is calculated as I set out in the original reply.

You find the "name" (if any) associated with a parameter index by using the sqlite3_bind_parameter_name API. Similarly, sqlite3_bind_parameter_index will use the provided name to return the associated index location.

The statement: SELECT ?, ?, ? has three parameters, numbered 1, 2, and 3. None of the parameters has a name.

The statement select ?, :whoopie, ? also has three parameters numbered 1, 2, and 3. Parameter 2 has a name ":whoopie". If you use to sqlite3_bind_parameter_index to find out which parameter has the name ":whoopie" it will return 2.

The statement SELECT ?45, :whoopie, ?2 has 46 parameters. Parameter 46 has the name ":whoopie", and if you look up the index of the parameter named ":whoopie" you will get the result that it is index 46. The statement only uses parameter numbers 45, 46, and 2. You may bind something to parameter 1 or 3 through 44 though those parameter contents are unused by the statement.

(7) By anonymous on 2021-01-12 12:47:43 in reply to 6 [link] [source]

:whoopie

Thanks, that fills another gap in my knowledge - namely whether parameters can be named; from what you say, they can.

(8.3) By Keith Medcalf (kmedcalf) on 2021-01-12 14:39:39 edited from 8.2 in reply to 7 [source]

Yes. All parameters are numbered.

The generic ? creates a generic numbered parameter where the "bind index" is one more than the largest "bind index" used in any parameter to its left in the statement -- the first "bind index" being 1 if no other number is specified.

The form ?NNN is a reference to bind index NNN.

The form :VVV, @VVV, and $VVV are also numbered parameters just as if they were the generic ? form -- except the name is assigned as the name of the numbered bind index the first time it is used, and is as if it were ?NNN where NNN refers to the bind index of the bind index number that was assigned that name the first time it was seen.

So SELECT :a, ?, ?35, ?2, :a has 35 bind index locations. The first (leftmost) parameter in the statement is at bind index 1 and has the name :a; the second parameter in the statement is at bind index 2 and has no name; the third parameter in the statement is at bind index 35; the fourth parameter is a reference to bind index 2; and, the fifth parameter is a reference to the parameter with bind index 1 (and has the name :a). Parameters at bind index 3 through 34 are unused.

(3) By Gunter Hick (gunter_hick) on 2021-01-12 09:57:55 in reply to 1 [link] [source]

"Can I simply use ? as the placeholder and use sqlite3_bind_text throughout (the prepared SQL statement is a literal, after all)."

No. Preparing an SQL Statements transforms a text representation into an internal representation (an SQL Program). SQL Parameters do NOT work like fancy printf() arguments.

Running the result of

printf("SELECT %d, typeof(%d);",i,i);

is different to preparing, binding and running

SELECT ?1,txpeof(?1);

unless you happen to bind the integer i to parameter 1.

NOTE: DO NOT build SQL Statements using strings input from the user. This opens you up to SQL injection attacks.