Determining valid statement placeholders.

classic Classic list List threaded Threaded
19 messages Options
Reply | Threaded
Open this post in threaded view
|

Determining valid statement placeholders.

test user
Is there a `sqlite3` C FFI API that allows me to determine which index
values are valid to bind to?

The `sqlite3_bind_parameter_*` APIs (count, name, index) do not actually
provide the valid indexes.

For example: "?, ?10" would have two valid indexes: (1, 10).

But how can I know that indexes 2-9 are invalid from the API?

I have tried binding null to 2-9, but get SQLITE_OK.

I want to be able to check clients have provided all data needed for a
query instead having SQLite bind null by default for placeholders with no
data binding.

Thanks.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Determining valid statement placeholders.

Simon Slavin-3
On 21 Jul 2019, at 8:03pm, test user <[hidden email]> wrote:

> Is there a `sqlite3` C FFI API that allows me to determine which index values are valid to bind to?

I think you're talking about one of these two:

int sqlite3_bind_parameter_index(sqlite3_stmt*, const char *zName);
const char *sqlite3_bind_parameter_name(sqlite3_stmt*, int);

Those are functions which can be used to convert between parameter numbers and the names of named parameters.

'index' here means the parameter number in a statement.  The first parameter has an index of 1.  And you can find out how many parameters a statement has using

int sqlite3_bind_parameter_count(sqlite3_stmt*);

So any integer between 1 and the count is valid.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Determining valid statement placeholders.

Simon Slavin-3
In reply to this post by test user
On 21 Jul 2019, at 8:03pm, test user <[hidden email]> wrote:

> For example: "?, ?10" would have two valid indexes: (1, 10).


Please don't use numbers as names.  That would be extremely confusing to anyone trying to understand your code.

> But how can I know that indexes 2-9 are invalid from the API?

You have no reason to believe they're valid.  You made the statement yourself.  You know what names were used in the statement.  Your statement's parameters can be referred to in two ways: by natural numbers (1, 2, 3 ...) or by the names you supplied.  If you have somehow lost the list of names you supplied you can use

int sqlite3_bind_parameter_count(sqlite3_stmt*);

to figure out how many parameters there are and then

const char *sqlite3_bind_parameter_name(sqlite3_stmt*, int);

to return the name of each one.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Determining valid statement placeholders.

Hick Gunter
In reply to this post by test user
No. As the creator of a statement, you are expected to know either the position or the name of any SQL parameters contained therein.
Determining which indexes are legal does not help in determining what has been bound, not even if anything has been bound. Please note that sqlite3_reset() does not clear the bindings.

What is the underlying use case?

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von test user
Gesendet: Sonntag, 21. Juli 2019 21:04
An: [hidden email]
Betreff: [EXTERNAL] [sqlite] Determining valid statement placeholders.

Is there a `sqlite3` C FFI API that allows me to determine which index values are valid to bind to?

The `sqlite3_bind_parameter_*` APIs (count, name, index) do not actually provide the valid indexes.

For example: "?, ?10" would have two valid indexes: (1, 10).

But how can I know that indexes 2-9 are invalid from the API?

I have tried binding null to 2-9, but get SQLITE_OK.

I want to be able to check clients have provided all data needed for a query instead having SQLite bind null by default for placeholders with no data binding.

Thanks.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Determining valid statement placeholders.

test user
Simon Slavin: "Please don't use numbers as names."

The manual states:
- "?NNN": where N is an *integer*.
- "?": Programmers are encouraged to use one of the symbolic formats below
or the ?NNN format above instead."


The use case:

I am creating a library that takes SQL from the application and runs it via
the SQLite FFI.

I want to be able to write this function:

if has_bound_all_placeholders_in_query_string() {
    // run query
} else {
    throw Error("You must bind all query parameters.")

    // This branch cannot be created with the current SQLite FFI APIs.
    // It is useful because the SQLite default is to bind NULL to unbound
placeholders, which may not be what the user wanted. If they wanted this
they would of just used the constant NULL instead of a placeholder.
    // In dynamic languages where both the query string and the data could
be dynamically generated, being able to enforce binding all placeholders is
useful for correctness.
}


A new function could be added:

For query string "SELECT ?, ?10"

`sqlite3_bind_parameter_valid_indexes() -> [1, 10]`

How can I propose adding this?

On Mon, Jul 22, 2019 at 6:52 AM Hick Gunter <[hidden email]> wrote:

> No. As the creator of a statement, you are expected to know either the
> position or the name of any SQL parameters contained therein.
> Determining which indexes are legal does not help in determining what has
> been bound, not even if anything has been bound. Please note that
> sqlite3_reset() does not clear the bindings.
>
> What is the underlying use case?
>
> -----Ursprüngliche Nachricht-----
> Von: sqlite-users [mailto:[hidden email]]
> Im Auftrag von test user
> Gesendet: Sonntag, 21. Juli 2019 21:04
> An: [hidden email]
> Betreff: [EXTERNAL] [sqlite] Determining valid statement placeholders.
>
> Is there a `sqlite3` C FFI API that allows me to determine which index
> values are valid to bind to?
>
> The `sqlite3_bind_parameter_*` APIs (count, name, index) do not actually
> provide the valid indexes.
>
> For example: "?, ?10" would have two valid indexes: (1, 10).
>
> But how can I know that indexes 2-9 are invalid from the API?
>
> I have tried binding null to 2-9, but get SQLITE_OK.
>
> I want to be able to check clients have provided all data needed for a
> query instead having SQLite bind null by default for placeholders with no
> data binding.
>
> Thanks.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___________________________________________
>  Gunter Hick | Software Engineer | Scientific Games International GmbH |
> Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O)
> +43 1 80100 - 0
>
> May be privileged. May be confidential. Please delete if not the addressee.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Determining valid statement placeholders.

Simon Slavin-3
On 22 Jul 2019, at 10:53am, test user <[hidden email]> wrote:

> A new function could be added:
>
> For query string "SELECT ?, ?10"
>
> `sqlite3_bind_parameter_valid_indexes() -> [1, 10]`
>
> How can I propose adding this?

As given in my previous response, the SQLite library already contains these two:

int sqlite3_bind_parameter_count(sqlite3_stmt*);
const char *sqlite3_bind_parameter_name(sqlite3_stmt*, int);

Which provide the same information.  If you want to create your own routine which calls them to make a list, that's fine.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Determining valid statement placeholders.

Enzo
It is not the same information.

I want to be able to determine "has the user bound all placeholder values
with data?".

The user provides this as input:

query="SELECT ?, ?10"
data={"0": "data-a", "10": "data-b"}

Note: This IS valid, as they have provided all data for placeholders.



Using the APIs you mentioned:

count=10
name(10) = "?10"


So according to the API, the indexes [1, 2, 3, 4, 5, 6, 7, 8, 9, 10] are
placeholders in the query.

As you can see, only indexes [1, 10] are valid placeholders in the query.


So, as far as I can tell, it is not possible to get this from the API:

query="SELECT ?, ?10"
valid_placeholder_indexes=[1, 10]

It is only possible to get this:

query="SELECT ?, ?10"
maybe_placeholder_indexes=[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]

On Mon, Jul 22, 2019 at 11:06 AM Simon Slavin <[hidden email]> wrote:

> On 22 Jul 2019, at 10:53am, test user <[hidden email]>
> wrote:
>
> > A new function could be added:
> >
> > For query string "SELECT ?, ?10"
> >
> > `sqlite3_bind_parameter_valid_indexes() -> [1, 10]`
> >
> > How can I propose adding this?
>
> As given in my previous response, the SQLite library already contains
> these two:
>
> int sqlite3_bind_parameter_count(sqlite3_stmt*);
> const char *sqlite3_bind_parameter_name(sqlite3_stmt*, int);
>
> Which provide the same information.  If you want to create your own
> routine which calls them to make a list, that's fine.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Determining valid statement placeholders.

Keith Medcalf

On Monday, 22 July, 2019 04:34, Enzo <[hidden email]> wrote:

>It is not the same information.

>I want to be able to determine "has the user bound all placeholder
>values with data?".

>The user provides this as input:

>query="SELECT ?, ?10"
>data={"0": "data-a", "10": "data-b"}

>Note: This IS valid, as they have provided all data for placeholders.

>Using the APIs you mentioned:

>count=10
>name(10) = "?10"

>So according to the API, the indexes [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
>are placeholders in the query.

>As you can see, only indexes [1, 10] are valid placeholders in the
>query.

>So, as far as I can tell, it is not possible to get this from the
>API:

>query="SELECT ?, ?10"
>valid_placeholder_indexes=[1, 10]

>It is only possible to get this:

>query="SELECT ?, ?10"
>maybe_placeholder_indexes=[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]

The fundamental issue is that you are confusing POSITIONAL parameters with NAMED parameters.  ?nnn where nnn is numeric describe POSITIONAL parameters, so in the query SELECT ?, ?10 you are using positional parameter 1 and positional parameter 10.  That implies that there are 10 positional parameters.  The fact that you are only using those positional parameters sparsely (only using positional parameter 1 and positional parameter 10) does not mean that you are using two parameters, it means that you are using 10, but only referencing 2 of them.

Said another way, you are declaring a function that looks like this:

int stmt(a, b, c, d, e, f, g, h, i, j)
   return a + j

and expecting to be able to call it as

z = stmt(1, 6)

expecting some magic to know that the second parameter is really parameter 10.

https://www.sqlite.org/lang_expr.html#varparam

There may be many positional parameters (like 999 in the default build) and sqlite3_bind_parameter_count returns the "index" of the greatest parameter number used in the statement.  Having created 10 parameters sqlite has absolutely no clue that you happen to be using only parameter 1 and parameter 10.  If you only needed 2 parameters you should have only created 2, not 10.  That is what NAMED parameters are for.

If you change from using positional (?) parameters to using named (:) or (@) what happens?

select :1, :10; should only create 2 parameters named :1 and :10 ...

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Determining valid statement placeholders.

test user
I understand the problem, but I want my library to be able to detect the
problem programatically.

Currently if a user mixes index-based and key-based placeholders, the only
thing a library using SQLite can do us run the query with unbound
placeholders set to null (the SQLite default).

Id like the ability to instead throw an error in this case.


I think SQLite internally knows how many placeholders are in the query at
parse time.

My question is how can I get the data via the API, or if it would be
considered to add a function to get this data?


On Mon, Jul 22, 2019 at 12:29 PM Keith Medcalf <[hidden email]> wrote:

>
> On Monday, 22 July, 2019 04:34, Enzo <[hidden email]> wrote:
>
> >It is not the same information.
>
> >I want to be able to determine "has the user bound all placeholder
> >values with data?".
>
> >The user provides this as input:
>
> >query="SELECT ?, ?10"
> >data={"0": "data-a", "10": "data-b"}
>
> >Note: This IS valid, as they have provided all data for placeholders.
>
> >Using the APIs you mentioned:
>
> >count=10
> >name(10) = "?10"
>
> >So according to the API, the indexes [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
> >are placeholders in the query.
>
> >As you can see, only indexes [1, 10] are valid placeholders in the
> >query.
>
> >So, as far as I can tell, it is not possible to get this from the
> >API:
>
> >query="SELECT ?, ?10"
> >valid_placeholder_indexes=[1, 10]
>
> >It is only possible to get this:
>
> >query="SELECT ?, ?10"
> >maybe_placeholder_indexes=[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
>
> The fundamental issue is that you are confusing POSITIONAL parameters with
> NAMED parameters.  ?nnn where nnn is numeric describe POSITIONAL
> parameters, so in the query SELECT ?, ?10 you are using positional
> parameter 1 and positional parameter 10.  That implies that there are 10
> positional parameters.  The fact that you are only using those positional
> parameters sparsely (only using positional parameter 1 and positional
> parameter 10) does not mean that you are using two parameters, it means
> that you are using 10, but only referencing 2 of them.
>
> Said another way, you are declaring a function that looks like this:
>
> int stmt(a, b, c, d, e, f, g, h, i, j)
>    return a + j
>
> and expecting to be able to call it as
>
> z = stmt(1, 6)
>
> expecting some magic to know that the second parameter is really parameter
> 10.
>
> https://www.sqlite.org/lang_expr.html#varparam
>
> There may be many positional parameters (like 999 in the default build)
> and sqlite3_bind_parameter_count returns the "index" of the greatest
> parameter number used in the statement.  Having created 10 parameters
> sqlite has absolutely no clue that you happen to be using only parameter 1
> and parameter 10.  If you only needed 2 parameters you should have only
> created 2, not 10.  That is what NAMED parameters are for.
>
> If you change from using positional (?) parameters to using named (:) or
> (@) what happens?
>
> select :1, :10; should only create 2 parameters named :1 and :10 ...
>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Determining valid statement placeholders.

Hick Gunter
In reply to this post by test user
The question you are asking is properly adressed by the logic that creates the statements itself. The statement generator itself needs to keep track of the SQL parameters it has created and that something has been bound for each parameter before issuing a call to sqlite3_step. Note that as far as SQlite is concerned, binding NULL ist equivalent to unbinding a parameter, whereas you are looking for a distinction between "never bound anything" and "bound NULL on purpose", so even if SQLite could tell you if a bound parameter was NULL or not, that still would not answer your question.

SQLite itself is only concerned with the total number of parameters required to execute the statement and their names, if given, in case you want to bind by name.

Or, in a nutshell, the data storage layer (SQLite) is neither designed for nor able to answer internals of the data processing layer (your query generator)

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von test user
Gesendet: Montag, 22. Juli 2019 11:53
An: SQLite mailing list <[hidden email]>
Betreff: Re: [sqlite] [EXTERNAL] Determining valid statement placeholders.

Simon Slavin: "Please don't use numbers as names."

The manual states:
- "?NNN": where N is an *integer*.
- "?": Programmers are encouraged to use one of the symbolic formats below or the ?NNN format above instead."


The use case:

I am creating a library that takes SQL from the application and runs it via the SQLite FFI.

I want to be able to write this function:

if has_bound_all_placeholders_in_query_string() {
    // run query
} else {
    throw Error("You must bind all query parameters.")

    // This branch cannot be created with the current SQLite FFI APIs.
    // It is useful because the SQLite default is to bind NULL to unbound placeholders, which may not be what the user wanted. If they wanted this they would of just used the constant NULL instead of a placeholder.
    // In dynamic languages where both the query string and the data could be dynamically generated, being able to enforce binding all placeholders is useful for correctness.
}


A new function could be added:

For query string "SELECT ?, ?10"

`sqlite3_bind_parameter_valid_indexes() -> [1, 10]`

How can I propose adding this?

On Mon, Jul 22, 2019 at 6:52 AM Hick Gunter <[hidden email]> wrote:

> No. As the creator of a statement, you are expected to know either the
> position or the name of any SQL parameters contained therein.
> Determining which indexes are legal does not help in determining what
> has been bound, not even if anything has been bound. Please note that
> sqlite3_reset() does not clear the bindings.
>
> What is the underlying use case?
>
> -----Ursprüngliche Nachricht-----
> Von: sqlite-users
> [mailto:[hidden email]]
> Im Auftrag von test user
> Gesendet: Sonntag, 21. Juli 2019 21:04
> An: [hidden email]
> Betreff: [EXTERNAL] [sqlite] Determining valid statement placeholders.
>
> Is there a `sqlite3` C FFI API that allows me to determine which index
> values are valid to bind to?
>
> The `sqlite3_bind_parameter_*` APIs (count, name, index) do not
> actually provide the valid indexes.
>
> For example: "?, ?10" would have two valid indexes: (1, 10).
>
> But how can I know that indexes 2-9 are invalid from the API?
>
> I have tried binding null to 2-9, but get SQLITE_OK.
>
> I want to be able to check clients have provided all data needed for a
> query instead having SQLite bind null by default for placeholders with
> no data binding.
>
> Thanks.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___________________________________________
>  Gunter Hick | Software Engineer | Scientific Games International GmbH
> | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013
> | (O)
> +43 1 80100 - 0
>
> May be privileged. May be confidential. Please delete if not the addressee.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Determining valid statement placeholders.

Richard Hipp-3
In reply to this post by test user
On 7/22/19, test user <[hidden email]> wrote:
>
> I think SQLite internally knows how many placeholders are in the query at
> parse time.

SQLite knows the *maximum" placeholder.  If you say:

     INSERT INTO t1(x) VALUES(?100);

Then it allocates an array of 100 placeholders.  It does not know that
the first 99 are unused.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Determining valid statement placeholders.

Hick Gunter
In reply to this post by test user
This cannot be determined programatically.

The query generator stores an OP_Variable opcode when the SQL program needs to access the contents of an SQL parameter, and keeps track of the highest parameter number used, which determines the size of the parameter array.

Even if you were to examine the generated bytecode to determine which variables are referenced and check if the referenced variable has a value, this would still not answer your question. And you would be excluding any query that requires NULL to be bound to an SQL parameter.

Mixing numbered and named parameters is a very bad idea. Either you care about the assigned indexes or you don't.

SELECT ?, ?10, ?2, :AAA;
SELECT ?, :AAA, ?10, ?2;

The above queries are different in much more than the order of the values returned.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von test user
Gesendet: Montag, 22. Juli 2019 14:36
An: SQLite mailing list <[hidden email]>
Betreff: Re: [sqlite] [EXTERNAL] Determining valid statement placeholders.

I understand the problem, but I want my library to be able to detect the problem programatically.

Currently if a user mixes index-based and key-based placeholders, the only thing a library using SQLite can do us run the query with unbound placeholders set to null (the SQLite default).

Id like the ability to instead throw an error in this case.


I think SQLite internally knows how many placeholders are in the query at parse time.

My question is how can I get the data via the API, or if it would be considered to add a function to get this data?


On Mon, Jul 22, 2019 at 12:29 PM Keith Medcalf <[hidden email]> wrote:

>
> On Monday, 22 July, 2019 04:34, Enzo <[hidden email]> wrote:
>
> >It is not the same information.
>
> >I want to be able to determine "has the user bound all placeholder
> >values with data?".
>
> >The user provides this as input:
>
> >query="SELECT ?, ?10"
> >data={"0": "data-a", "10": "data-b"}
>
> >Note: This IS valid, as they have provided all data for placeholders.
>
> >Using the APIs you mentioned:
>
> >count=10
> >name(10) = "?10"
>
> >So according to the API, the indexes [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
> >are placeholders in the query.
>
> >As you can see, only indexes [1, 10] are valid placeholders in the
> >query.
>
> >So, as far as I can tell, it is not possible to get this from the
> >API:
>
> >query="SELECT ?, ?10"
> >valid_placeholder_indexes=[1, 10]
>
> >It is only possible to get this:
>
> >query="SELECT ?, ?10"
> >maybe_placeholder_indexes=[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
>
> The fundamental issue is that you are confusing POSITIONAL parameters
> with NAMED parameters.  ?nnn where nnn is numeric describe POSITIONAL
> parameters, so in the query SELECT ?, ?10 you are using positional
> parameter 1 and positional parameter 10.  That implies that there are
> 10 positional parameters.  The fact that you are only using those
> positional parameters sparsely (only using positional parameter 1 and
> positional parameter 10) does not mean that you are using two
> parameters, it means that you are using 10, but only referencing 2 of them.
>
> Said another way, you are declaring a function that looks like this:
>
> int stmt(a, b, c, d, e, f, g, h, i, j)
>    return a + j
>
> and expecting to be able to call it as
>
> z = stmt(1, 6)
>
> expecting some magic to know that the second parameter is really
> parameter 10.
>
> https://www.sqlite.org/lang_expr.html#varparam
>
> There may be many positional parameters (like 999 in the default
> build) and sqlite3_bind_parameter_count returns the "index" of the
> greatest parameter number used in the statement.  Having created 10
> parameters sqlite has absolutely no clue that you happen to be using
> only parameter 1 and parameter 10.  If you only needed 2 parameters
> you should have only created 2, not 10.  That is what NAMED parameters are for.
>
> If you change from using positional (?) parameters to using named (:)
> or
> (@) what happens?
>
> select :1, :10; should only create 2 parameters named :1 and :10 ...
>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven
> says a lot about anticipated traffic volume.
>
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [SPAM?] Re: [EXTERNAL] Determining valid statement placeholders.

Richard Damon
In reply to this post by test user
The problem is you are defining your problem differently than SQLite does, so it can’t help you.

To SQLite, a query with ?10 in it has (at least) 10 positional parameters, and if you are defining that the user needs to explicitly provide values for all parameters, (by SQLite) that means they will need to provide positional parameters 1 - 10 (even if some are not used).

You seem to want to redefine that in this case, if a parameter isn’t actually used, then it doesn’t need to be provided. To do this, my guess is you are going to need to parse the SQL yourself (at least partially) to detect what parameters are used.

Note, perhaps part of the issue is that you seem to want to call ?10 as a key based placeholder, but to SQLite it is an index based placeholder, and implies that indexes 1 through 9 also exist.

> On Jul 22, 2019, at 8:36 AM, test user <[hidden email]> wrote:
>
> I understand the problem, but I want my library to be able to detect the
> problem programatically.
>
> Currently if a user mixes index-based and key-based placeholders, the only
> thing a library using SQLite can do us run the query with unbound
> placeholders set to null (the SQLite default).
>
> Id like the ability to instead throw an error in this case.
>
>
> I think SQLite internally knows how many placeholders are in the query at
> parse time.
>
> My question is how can I get the data via the API, or if it would be
> considered to add a function to get this data?
>
>
>> On Mon, Jul 22, 2019 at 12:29 PM Keith Medcalf <[hidden email]> wrote:
>>
>>
>>> On Monday, 22 July, 2019 04:34, Enzo <[hidden email]> wrote:
>>>
>>> It is not the same information.
>>
>>> I want to be able to determine "has the user bound all placeholder
>>> values with data?".
>>
>>> The user provides this as input:
>>
>>> query="SELECT ?, ?10"
>>> data={"0": "data-a", "10": "data-b"}
>>
>>> Note: This IS valid, as they have provided all data for placeholders.
>>
>>> Using the APIs you mentioned:
>>
>>> count=10
>>> name(10) = "?10"
>>
>>> So according to the API, the indexes [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
>>> are placeholders in the query.
>>
>>> As you can see, only indexes [1, 10] are valid placeholders in the
>>> query.
>>
>>> So, as far as I can tell, it is not possible to get this from the
>>> API:
>>
>>> query="SELECT ?, ?10"
>>> valid_placeholder_indexes=[1, 10]
>>
>>> It is only possible to get this:
>>
>>> query="SELECT ?, ?10"
>>> maybe_placeholder_indexes=[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
>>
>> The fundamental issue is that you are confusing POSITIONAL parameters with
>> NAMED parameters.  ?nnn where nnn is numeric describe POSITIONAL
>> parameters, so in the query SELECT ?, ?10 you are using positional
>> parameter 1 and positional parameter 10.  That implies that there are 10
>> positional parameters.  The fact that you are only using those positional
>> parameters sparsely (only using positional parameter 1 and positional
>> parameter 10) does not mean that you are using two parameters, it means
>> that you are using 10, but only referencing 2 of them.
>>
>> Said another way, you are declaring a function that looks like this:
>>
>> int stmt(a, b, c, d, e, f, g, h, i, j)
>>   return a + j
>>
>> and expecting to be able to call it as
>>
>> z = stmt(1, 6)
>>
>> expecting some magic to know that the second parameter is really parameter
>> 10.
>>
>> https://www.sqlite.org/lang_expr.html#varparam
>>
>> There may be many positional parameters (like 999 in the default build)
>> and sqlite3_bind_parameter_count returns the "index" of the greatest
>> parameter number used in the statement.  Having created 10 parameters
>> sqlite has absolutely no clue that you happen to be using only parameter 1
>> and parameter 10.  If you only needed 2 parameters you should have only
>> created 2, not 10.  That is what NAMED parameters are for.
>>
>> If you change from using positional (?) parameters to using named (:) or
>> (@) what happens?
>>
>> select :1, :10; should only create 2 parameters named :1 and :10 ...
>>
>> --
>> The fact that there's a Highway to Hell but only a Stairway to Heaven says
>> a lot about anticipated traffic volume.
>>
>>
>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Determining valid statement placeholders.

Keith Medcalf
In reply to this post by test user

I don't see what is so hard.  APSW does it:

>python
Python 2.7.16 (v2.7.16:413a49145e, Mar  4 2019, 01:30:55) [MSC v.1500 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import apsw
>>> db = apsw.Connection('')
>>> db.execute('select ?, ?10;', ('one', 'ten')).fetchone()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "C:\python\lib\site-packages\Local\newapsw.py", line 39, in execute
    return self.cursor().execute(*args, **kwargs)
apsw.BindingsError: Incorrect number of bindings supplied.  The current statement uses 10 and there are 2 supplied.  Current offset is 0
>>>

The statement required 10 parameters and only 2 were supplied.  ERROR!

Internally it uses sqlite3_bind_parameter_count to find out how many parameters need binding and requires that the number of "parameters" passed match the number of parameters "expected" when binding positionally.

>>> db.execute('select :1, :10;', {'2': 'one', '10': 'ten'}).fetchone()
Row(_0=None, _1=u'ten')
>>> db.execute('select :1, :10;', {'1': 'one', '10': 'ten'}).fetchone()
Row(_0=u'one', _1=u'ten')
>>>

When binding by name, it looks up the names in the provided dictionary and binds those it finds.

You can also bind named parameters to a positional list (in which case it is the programmers job to keep track of what they are doing) since a named parameter is merely syntactic sugar on top of positional parameters:

>>> db.execute('select :1, :10;', ('one', 'two')).fetchone()
Row(_0=u'one', _1=u'two')
>>>                                                                                                                                                    

It is not very difficult.  You call sqlite3_bind_parameter_count.  Then if you are binding positionally you make sure there are sufficient positional parameters provided to bind them all.  It you are binding by name, you cycle through the parameters, get the name, and then bind the given named parameter to that parameter.  apsw chooses to ignore missing items when binding by name -- sounds like you simply want to ERROR instead ...

The only issue I can see is that when requesting the name of an positional parameter that has no name it returns null rather than the positional name, however, this is pretty easy to work around in pretty much any programming language ... that is if sqlite3_parameter_name(stmt, x) return null then the name is a ? followed by x in decimal (sprintf("?%d", x) or thereabouts having appropriate accomodations for the language syntax and buffer safety, etc)

However, you cannot bind to positional parameters by name:

>>> db.execute('select ?1, ?10;', {'2': 'one', '10': 'ten'}).fetchone()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "C:\python\lib\site-packages\Local\newapsw.py", line 39, in execute
    return self.cursor().execute(*args, **kwargs)
apsw.BindingsError: Binding 1 has no name, but you supplied a dict (which only has names).


*the builtin python sqlite3 wrapper does the same thing I expect (though I have never actually looked) but it is somewhat braindead otherwise, so I don't use it and haven't bothered to test what it does in these circumstances.  Though my expectation is that it would behave somewhat similarly.
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of test user
>Sent: Monday, 22 July, 2019 06:36
>To: SQLite mailing list
>Subject: Re: [sqlite] [EXTERNAL] Determining valid statement
>placeholders.
>
>I understand the problem, but I want my library to be able to detect
>the
>problem programatically.
>
>Currently if a user mixes index-based and key-based placeholders, the
>only
>thing a library using SQLite can do us run the query with unbound
>placeholders set to null (the SQLite default).
>
>Id like the ability to instead throw an error in this case.
>
>
>I think SQLite internally knows how many placeholders are in the
>query at
>parse time.
>
>My question is how can I get the data via the API, or if it would be
>considered to add a function to get this data?
>
>
>On Mon, Jul 22, 2019 at 12:29 PM Keith Medcalf <[hidden email]>
>wrote:
>
>>
>> On Monday, 22 July, 2019 04:34, Enzo <[hidden email]>
>wrote:
>>
>> >It is not the same information.
>>
>> >I want to be able to determine "has the user bound all placeholder
>> >values with data?".
>>
>> >The user provides this as input:
>>
>> >query="SELECT ?, ?10"
>> >data={"0": "data-a", "10": "data-b"}
>>
>> >Note: This IS valid, as they have provided all data for
>placeholders.
>>
>> >Using the APIs you mentioned:
>>
>> >count=10
>> >name(10) = "?10"
>>
>> >So according to the API, the indexes [1, 2, 3, 4, 5, 6, 7, 8, 9,
>10]
>> >are placeholders in the query.
>>
>> >As you can see, only indexes [1, 10] are valid placeholders in the
>> >query.
>>
>> >So, as far as I can tell, it is not possible to get this from the
>> >API:
>>
>> >query="SELECT ?, ?10"
>> >valid_placeholder_indexes=[1, 10]
>>
>> >It is only possible to get this:
>>
>> >query="SELECT ?, ?10"
>> >maybe_placeholder_indexes=[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
>>
>> The fundamental issue is that you are confusing POSITIONAL
>parameters with
>> NAMED parameters.  ?nnn where nnn is numeric describe POSITIONAL
>> parameters, so in the query SELECT ?, ?10 you are using positional
>> parameter 1 and positional parameter 10.  That implies that there
>are 10
>> positional parameters.  The fact that you are only using those
>positional
>> parameters sparsely (only using positional parameter 1 and
>positional
>> parameter 10) does not mean that you are using two parameters, it
>means
>> that you are using 10, but only referencing 2 of them.
>>
>> Said another way, you are declaring a function that looks like
>this:
>>
>> int stmt(a, b, c, d, e, f, g, h, i, j)
>>    return a + j
>>
>> and expecting to be able to call it as
>>
>> z = stmt(1, 6)
>>
>> expecting some magic to know that the second parameter is really
>parameter
>> 10.
>>
>> https://www.sqlite.org/lang_expr.html#varparam
>>
>> There may be many positional parameters (like 999 in the default
>build)
>> and sqlite3_bind_parameter_count returns the "index" of the
>greatest
>> parameter number used in the statement.  Having created 10
>parameters
>> sqlite has absolutely no clue that you happen to be using only
>parameter 1
>> and parameter 10.  If you only needed 2 parameters you should have
>only
>> created 2, not 10.  That is what NAMED parameters are for.
>>
>> If you change from using positional (?) parameters to using named
>(:) or
>> (@) what happens?
>>
>> select :1, :10; should only create 2 parameters named :1 and :10
>...
>>
>> --
>> The fact that there's a Highway to Hell but only a Stairway to
>Heaven says
>> a lot about anticipated traffic volume.
>>
>>
>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Determining valid statement placeholders.

Clemens Ladisch
In reply to this post by test user
test user wrote:
> I want my library to be able to detect the problem programatically.
>
> I think SQLite internally knows how many placeholders are in the query at
> parse time.
>
> My question is how can I get the data via the API

At the moment, there is no such mechanism in the API.

You could parse the output of EXPLAIN (look at the p1 values for
opcode = 'Variable'), but that is not guaranteed to work in future
versions.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [SPAM?] Re: [EXTERNAL] Determining valid statement placeholders.

Richard Damon
In reply to this post by Keith Medcalf
But he was to be able to provide the args as {‘0’:’one, ‘10’:’ten’} and since he only uses positional rags 1 and 10 that it be OK, i.e. positional args are treated as key word args, and only those actually used are needed. THAT can’t be provided by the API.

> On Jul 22, 2019, at 9:22 AM, Keith Medcalf <[hidden email]> wrote:
>
>
> I don't see what is so hard.  APSW does it:
>
>> python
> Python 2.7.16 (v2.7.16:413a49145e, Mar  4 2019, 01:30:55) [MSC v.1500 32 bit (Intel)] on win32
> Type "help", "copyright", "credits" or "license" for more information.
>>>> import apsw
>>>> db = apsw.Connection('')
>>>> db.execute('select ?, ?10;', ('one', 'ten')).fetchone()
> Traceback (most recent call last):
>  File "<stdin>", line 1, in <module>
>  File "C:\python\lib\site-packages\Local\newapsw.py", line 39, in execute
>    return self.cursor().execute(*args, **kwargs)
> apsw.BindingsError: Incorrect number of bindings supplied.  The current statement uses 10 and there are 2 supplied.  Current offset is 0
>>>>
>
> The statement required 10 parameters and only 2 were supplied.  ERROR!
>
> Internally it uses sqlite3_bind_parameter_count to find out how many parameters need binding and requires that the number of "parameters" passed match the number of parameters "expected" when binding positionally.
>
>>>> db.execute('select :1, :10;', {'2': 'one', '10': 'ten'}).fetchone()
> Row(_0=None, _1=u'ten')
>>>> db.execute('select :1, :10;', {'1': 'one', '10': 'ten'}).fetchone()
> Row(_0=u'one', _1=u'ten')
>>>>
>
> When binding by name, it looks up the names in the provided dictionary and binds those it finds.
>
> You can also bind named parameters to a positional list (in which case it is the programmers job to keep track of what they are doing) since a named parameter is merely syntactic sugar on top of positional parameters:
>
>>>> db.execute('select :1, :10;', ('one', 'two')).fetchone()
> Row(_0=u'one', _1=u'two')
>>>>
>
> It is not very difficult.  You call sqlite3_bind_parameter_count.  Then if you are binding positionally you make sure there are sufficient positional parameters provided to bind them all.  It you are binding by name, you cycle through the parameters, get the name, and then bind the given named parameter to that parameter.  apsw chooses to ignore missing items when binding by name -- sounds like you simply want to ERROR instead ...
>
> The only issue I can see is that when requesting the name of an positional parameter that has no name it returns null rather than the positional name, however, this is pretty easy to work around in pretty much any programming language ... that is if sqlite3_parameter_name(stmt, x) return null then the name is a ? followed by x in decimal (sprintf("?%d", x) or thereabouts having appropriate accomodations for the language syntax and buffer safety, etc)
>
> However, you cannot bind to positional parameters by name:
>
>>>> db.execute('select ?1, ?10;', {'2': 'one', '10': 'ten'}).fetchone()
> Traceback (most recent call last):
>  File "<stdin>", line 1, in <module>
>  File "C:\python\lib\site-packages\Local\newapsw.py", line 39, in execute
>    return self.cursor().execute(*args, **kwargs)
> apsw.BindingsError: Binding 1 has no name, but you supplied a dict (which only has names).
>
>
> *the builtin python sqlite3 wrapper does the same thing I expect (though I have never actually looked) but it is somewhat braindead otherwise, so I don't use it and haven't bothered to test what it does in these circumstances.  Though my expectation is that it would behave somewhat similarly.
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.
>
>
>> -----Original Message-----
>> From: sqlite-users [mailto:sqlite-users-
>> [hidden email]] On Behalf Of test user
>> Sent: Monday, 22 July, 2019 06:36
>> To: SQLite mailing list
>> Subject: Re: [sqlite] [EXTERNAL] Determining valid statement
>> placeholders.
>>
>> I understand the problem, but I want my library to be able to detect
>> the
>> problem programatically.
>>
>> Currently if a user mixes index-based and key-based placeholders, the
>> only
>> thing a library using SQLite can do us run the query with unbound
>> placeholders set to null (the SQLite default).
>>
>> Id like the ability to instead throw an error in this case.
>>
>>
>> I think SQLite internally knows how many placeholders are in the
>> query at
>> parse time.
>>
>> My question is how can I get the data via the API, or if it would be
>> considered to add a function to get this data?
>>
>>
>> On Mon, Jul 22, 2019 at 12:29 PM Keith Medcalf <[hidden email]>
>> wrote:
>>
>>>
>>> On Monday, 22 July, 2019 04:34, Enzo <[hidden email]>
>> wrote:
>>>
>>>> It is not the same information.
>>>
>>>> I want to be able to determine "has the user bound all placeholder
>>>> values with data?".
>>>
>>>> The user provides this as input:
>>>
>>>> query="SELECT ?, ?10"
>>>> data={"0": "data-a", "10": "data-b"}
>>>
>>>> Note: This IS valid, as they have provided all data for
>> placeholders.
>>>
>>>> Using the APIs you mentioned:
>>>
>>>> count=10
>>>> name(10) = "?10"
>>>
>>>> So according to the API, the indexes [1, 2, 3, 4, 5, 6, 7, 8, 9,
>> 10]
>>>> are placeholders in the query.
>>>
>>>> As you can see, only indexes [1, 10] are valid placeholders in the
>>>> query.
>>>
>>>> So, as far as I can tell, it is not possible to get this from the
>>>> API:
>>>
>>>> query="SELECT ?, ?10"
>>>> valid_placeholder_indexes=[1, 10]
>>>
>>>> It is only possible to get this:
>>>
>>>> query="SELECT ?, ?10"
>>>> maybe_placeholder_indexes=[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
>>>
>>> The fundamental issue is that you are confusing POSITIONAL
>> parameters with
>>> NAMED parameters.  ?nnn where nnn is numeric describe POSITIONAL
>>> parameters, so in the query SELECT ?, ?10 you are using positional
>>> parameter 1 and positional parameter 10.  That implies that there
>> are 10
>>> positional parameters.  The fact that you are only using those
>> positional
>>> parameters sparsely (only using positional parameter 1 and
>> positional
>>> parameter 10) does not mean that you are using two parameters, it
>> means
>>> that you are using 10, but only referencing 2 of them.
>>>
>>> Said another way, you are declaring a function that looks like
>> this:
>>>
>>> int stmt(a, b, c, d, e, f, g, h, i, j)
>>>   return a + j
>>>
>>> and expecting to be able to call it as
>>>
>>> z = stmt(1, 6)
>>>
>>> expecting some magic to know that the second parameter is really
>> parameter
>>> 10.
>>>
>>> https://www.sqlite.org/lang_expr.html#varparam
>>>
>>> There may be many positional parameters (like 999 in the default
>> build)
>>> and sqlite3_bind_parameter_count returns the "index" of the
>> greatest
>>> parameter number used in the statement.  Having created 10
>> parameters
>>> sqlite has absolutely no clue that you happen to be using only
>> parameter 1
>>> and parameter 10.  If you only needed 2 parameters you should have
>> only
>>> created 2, not 10.  That is what NAMED parameters are for.
>>>
>>> If you change from using positional (?) parameters to using named
>> (:) or
>>> (@) what happens?
>>>
>>> select :1, :10; should only create 2 parameters named :1 and :10
>> ...
>>>
>>> --
>>> The fact that there's a Highway to Hell but only a Stairway to
>> Heaven says
>>> a lot about anticipated traffic volume.
>>>
>>>
>>>
>>>
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>> users
>>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Determining valid statement placeholders.

curmudgeon
In reply to this post by test user
Would sqlite3_expanded_sql(stmt) be of any use?



https://sqlite.org/c3ref/expanded_sql.html





________________________________
From: sqlite-users <[hidden email]> on behalf of test user <[hidden email]>
Sent: Monday, July 22, 2019 1:36:25 PM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] [EXTERNAL] Determining valid statement placeholders.

I understand the problem, but I want my library to be able to detect the
problem programatically.

Currently if a user mixes index-based and key-based placeholders, the only
thing a library using SQLite can do us run the query with unbound
placeholders set to null (the SQLite default).

Id like the ability to instead throw an error in this case.


I think SQLite internally knows how many placeholders are in the query at
parse time.

My question is how can I get the data via the API, or if it would be
considered to add a function to get this data?


On Mon, Jul 22, 2019 at 12:29 PM Keith Medcalf <[hidden email]> wrote:

>
> On Monday, 22 July, 2019 04:34, Enzo <[hidden email]> wrote:
>
> >It is not the same information.
>
> >I want to be able to determine "has the user bound all placeholder
> >values with data?".
>
> >The user provides this as input:
>
> >query="SELECT ?, ?10"
> >data={"0": "data-a", "10": "data-b"}
>
> >Note: This IS valid, as they have provided all data for placeholders.
>
> >Using the APIs you mentioned:
>
> >count=10
> >name(10) = "?10"
>
> >So according to the API, the indexes [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
> >are placeholders in the query.
>
> >As you can see, only indexes [1, 10] are valid placeholders in the
> >query.
>
> >So, as far as I can tell, it is not possible to get this from the
> >API:
>
> >query="SELECT ?, ?10"
> >valid_placeholder_indexes=[1, 10]
>
> >It is only possible to get this:
>
> >query="SELECT ?, ?10"
> >maybe_placeholder_indexes=[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
>
> The fundamental issue is that you are confusing POSITIONAL parameters with
> NAMED parameters.  ?nnn where nnn is numeric describe POSITIONAL
> parameters, so in the query SELECT ?, ?10 you are using positional
> parameter 1 and positional parameter 10.  That implies that there are 10
> positional parameters.  The fact that you are only using those positional
> parameters sparsely (only using positional parameter 1 and positional
> parameter 10) does not mean that you are using two parameters, it means
> that you are using 10, but only referencing 2 of them.
>
> Said another way, you are declaring a function that looks like this:
>
> int stmt(a, b, c, d, e, f, g, h, i, j)
>    return a + j
>
> and expecting to be able to call it as
>
> z = stmt(1, 6)
>
> expecting some magic to know that the second parameter is really parameter
> 10.
>
> https://www.sqlite.org/lang_expr.html#varparam
>
> There may be many positional parameters (like 999 in the default build)
> and sqlite3_bind_parameter_count returns the "index" of the greatest
> parameter number used in the statement.  Having created 10 parameters
> sqlite has absolutely no clue that you happen to be using only parameter 1
> and parameter 10.  If you only needed 2 parameters you should have only
> created 2, not 10.  That is what NAMED parameters are for.
>
> If you change from using positional (?) parameters to using named (:) or
> (@) what happens?
>
> select :1, :10; should only create 2 parameters named :1 and :10 ...
>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [SPAM?] Re: [EXTERNAL] Determining valid statement placeholders.

Keith Medcalf
In reply to this post by Richard Damon

On Monday, 22 July, 2019 08:18, Richard Damon <[hidden email]>:

>But he was to be able to provide the args as {‘0’:’one, ‘10’:’ten’}
>and since he only uses positional rags 1 and 10 that it be OK, i.e.
>positional args are treated as key word args, and only those actually
>used are needed. THAT can’t be provided by the API.

Well, actually, it is not valid.  No clue what he is doing but it is VERY inconsistent.  The first positional argument is numbered 1 not 0, and the ?10 only has that name because it was a referenced positional.  If you have a statement like:

select ?, ?, ?, ?, ?10, ?1, ?2, ?50

you require 50 positional arguments.  Assuming you bind for(x=1; x<=50; x++) sqlite3_bind_int(stmt, x, x) then the result of the execution will be:

1 2 3 4 10 1 2 50

and positional parameters 1, 2, 10, and 50 will have names ?1, ?2, ?10, and ?50 respectively and positional parameters 3, 4, 5, 6, 7, 8, 9, and 11 through 49 will be unnamed.  unnamed parameters 3 and 4 are used in the query despite not having names.  unnamed parameters 5 through 9 and 11 through 49 are not accessed but obviously are expected to exist (otherwise why did the programmer request that they be created?)

In other words, names that start with '?' should be treated as if the name were null because those names have zero meaning.  The only meaning is the positional which was used to retrieve the fact that there was no valid name (valid names will commence with : @ $).

The programmer is expected to know what they are doing.  If they do not, then the correct expectation is that an ERROR will be thrown.  The programmer has said (in the above select) that 50 positional arguments are required.  If the programmer fails to provide them, then that is a programmer error and the computer should execute the "halt and execute programmer with extreme prejudice" instruction.  It is not for the computer to "guess" what the programmer meant ... this is not PL/1.  Down that road there be dragons. (though PL/1 is a very nice language)

The :name prefix was originally used to embed host variable name into EXEC SQL statements and that is how the EXEC SQL pre-processor knew what host variable in the current scope to bind.  This was maintained to allow "naming" of positional parameters.  Don't know where the @ came from (probably Sybase TRANSACT-SQL which was appropriated by Microsoft and became SQL Server).  The $name is because that is how TCL variables are accessed and SQLite3 was originally a TCL extension.

>> On Jul 22, 2019, at 9:22 AM, Keith Medcalf <[hidden email]>
>wrote:
>>
>>
>> I don't see what is so hard.  APSW does it:
>>
>>> python
>> Python 2.7.16 (v2.7.16:413a49145e, Mar  4 2019, 01:30:55) [MSC
>v.1500 32 bit (Intel)] on win32
>> Type "help", "copyright", "credits" or "license" for more
>information.
>>>>> import apsw
>>>>> db = apsw.Connection('')
>>>>> db.execute('select ?, ?10;', ('one', 'ten')).fetchone()
>> Traceback (most recent call last):
>>  File "<stdin>", line 1, in <module>
>>  File "C:\python\lib\site-packages\Local\newapsw.py", line 39, in
>execute
>>    return self.cursor().execute(*args, **kwargs)
>> apsw.BindingsError: Incorrect number of bindings supplied.  The
>current statement uses 10 and there are 2 supplied.  Current offset
>is 0
>>>>>
>>
>> The statement required 10 parameters and only 2 were supplied.
>ERROR!
>>
>> Internally it uses sqlite3_bind_parameter_count to find out how
>many parameters need binding and requires that the number of
>"parameters" passed match the number of parameters "expected" when
>binding positionally.
>>
>>>>> db.execute('select :1, :10;', {'2': 'one', '10':
>'ten'}).fetchone()
>> Row(_0=None, _1=u'ten')
>>>>> db.execute('select :1, :10;', {'1': 'one', '10':
>'ten'}).fetchone()
>> Row(_0=u'one', _1=u'ten')
>>>>>
>>
>> When binding by name, it looks up the names in the provided
>dictionary and binds those it finds.
>>
>> You can also bind named parameters to a positional list (in which
>case it is the programmers job to keep track of what they are doing)
>since a named parameter is merely syntactic sugar on top of
>positional parameters:
>>
>>>>> db.execute('select :1, :10;', ('one', 'two')).fetchone()
>> Row(_0=u'one', _1=u'two')
>>>>>
>>
>> It is not very difficult.  You call sqlite3_bind_parameter_count.
>Then if you are binding positionally you make sure there are
>sufficient positional parameters provided to bind them all.  It you
>are binding by name, you cycle through the parameters, get the name,
>and then bind the given named parameter to that parameter.  apsw
>chooses to ignore missing items when binding by name -- sounds like
>you simply want to ERROR instead ...
>>
>> The only issue I can see is that when requesting the name of an
>positional parameter that has no name it returns null rather than the
>positional name, however, this is pretty easy to work around in
>pretty much any programming language ... that is if
>sqlite3_parameter_name(stmt, x) return null then the name is a ?
>followed by x in decimal (sprintf("?%d", x) or thereabouts having
>appropriate accomodations for the language syntax and buffer safety,
>etc)
>>
>> However, you cannot bind to positional parameters by name:
>>
>>>>> db.execute('select ?1, ?10;', {'2': 'one', '10':
>'ten'}).fetchone()
>> Traceback (most recent call last):
>>  File "<stdin>", line 1, in <module>
>>  File "C:\python\lib\site-packages\Local\newapsw.py", line 39, in
>execute
>>    return self.cursor().execute(*args, **kwargs)
>> apsw.BindingsError: Binding 1 has no name, but you supplied a dict
>(which only has names).
>>
>>
>> *the builtin python sqlite3 wrapper does the same thing I expect
>(though I have never actually looked) but it is somewhat braindead
>otherwise, so I don't use it and haven't bothered to test what it
>does in these circumstances.  Though my expectation is that it would
>behave somewhat similarly.
>> --
>> The fact that there's a Highway to Hell but only a Stairway to
>Heaven says a lot about anticipated traffic volume.
>>
>>
>>> -----Original Message-----
>>> From: sqlite-users [mailto:sqlite-users-
>>> [hidden email]] On Behalf Of test user
>>> Sent: Monday, 22 July, 2019 06:36
>>> To: SQLite mailing list
>>> Subject: Re: [sqlite] [EXTERNAL] Determining valid statement
>>> placeholders.
>>>
>>> I understand the problem, but I want my library to be able to
>detect
>>> the
>>> problem programatically.
>>>
>>> Currently if a user mixes index-based and key-based placeholders,
>the
>>> only
>>> thing a library using SQLite can do us run the query with unbound
>>> placeholders set to null (the SQLite default).
>>>
>>> Id like the ability to instead throw an error in this case.
>>>
>>>
>>> I think SQLite internally knows how many placeholders are in the
>>> query at
>>> parse time.
>>>
>>> My question is how can I get the data via the API, or if it would
>be
>>> considered to add a function to get this data?
>>>
>>>
>>> On Mon, Jul 22, 2019 at 12:29 PM Keith Medcalf
><[hidden email]>
>>> wrote:
>>>
>>>>
>>>> On Monday, 22 July, 2019 04:34, Enzo <[hidden email]>
>>> wrote:
>>>>
>>>>> It is not the same information.
>>>>
>>>>> I want to be able to determine "has the user bound all
>placeholder
>>>>> values with data?".
>>>>
>>>>> The user provides this as input:
>>>>
>>>>> query="SELECT ?, ?10"
>>>>> data={"0": "data-a", "10": "data-b"}
>>>>
>>>>> Note: This IS valid, as they have provided all data for
>>> placeholders.
>>>>
>>>>> Using the APIs you mentioned:
>>>>
>>>>> count=10
>>>>> name(10) = "?10"
>>>>
>>>>> So according to the API, the indexes [1, 2, 3, 4, 5, 6, 7, 8, 9,
>>> 10]
>>>>> are placeholders in the query.
>>>>
>>>>> As you can see, only indexes [1, 10] are valid placeholders in
>the
>>>>> query.
>>>>
>>>>> So, as far as I can tell, it is not possible to get this from
>the
>>>>> API:
>>>>
>>>>> query="SELECT ?, ?10"
>>>>> valid_placeholder_indexes=[1, 10]
>>>>
>>>>> It is only possible to get this:
>>>>
>>>>> query="SELECT ?, ?10"
>>>>> maybe_placeholder_indexes=[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
>>>>
>>>> The fundamental issue is that you are confusing POSITIONAL
>>> parameters with
>>>> NAMED parameters.  ?nnn where nnn is numeric describe POSITIONAL
>>>> parameters, so in the query SELECT ?, ?10 you are using
>positional
>>>> parameter 1 and positional parameter 10.  That implies that there
>>> are 10
>>>> positional parameters.  The fact that you are only using those
>>> positional
>>>> parameters sparsely (only using positional parameter 1 and
>>> positional
>>>> parameter 10) does not mean that you are using two parameters, it
>>> means
>>>> that you are using 10, but only referencing 2 of them.
>>>>
>>>> Said another way, you are declaring a function that looks like
>>> this:
>>>>
>>>> int stmt(a, b, c, d, e, f, g, h, i, j)
>>>>   return a + j
>>>>
>>>> and expecting to be able to call it as
>>>>
>>>> z = stmt(1, 6)
>>>>
>>>> expecting some magic to know that the second parameter is really
>>> parameter
>>>> 10.
>>>>
>>>> https://www.sqlite.org/lang_expr.html#varparam
>>>>
>>>> There may be many positional parameters (like 999 in the default
>>> build)
>>>> and sqlite3_bind_parameter_count returns the "index" of the
>>> greatest
>>>> parameter number used in the statement.  Having created 10
>>> parameters
>>>> sqlite has absolutely no clue that you happen to be using only
>>> parameter 1
>>>> and parameter 10.  If you only needed 2 parameters you should
>have
>>> only
>>>> created 2, not 10.  That is what NAMED parameters are for.
>>>>
>>>> If you change from using positional (?) parameters to using named
>>> (:) or
>>>> (@) what happens?
>>>>
>>>> select :1, :10; should only create 2 parameters named :1 and :10
>>> ...
>>>>
>>>> --
>>>> The fact that there's a Highway to Hell but only a Stairway to
>>> Heaven says
>>>> a lot about anticipated traffic volume.
>>>>
>>>>
>>>>
>>>>
>>>> _______________________________________________
>>>> sqlite-users mailing list
>>>> [hidden email]
>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>>> users
>>>>
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Determining valid statement placeholders.

test user
In reply to this post by Keith Medcalf
Thanks Keith, I think you are right.

I can enforce only using index-based or key-based placeholders and force
the user to supply data as an array (indexed) or an object (keyed).

I think I was assuming I would allow treating index-based placeholders as
keys {"?10": "data"}, which is where the "detect valid placeholders" need
came from.

Thanks for the help.




On Mon, Jul 22, 2019 at 2:23 PM Keith Medcalf <[hidden email]> wrote:

>
> I don't see what is so hard.  APSW does it:
>
> >python
> Python 2.7.16 (v2.7.16:413a49145e, Mar  4 2019, 01:30:55) [MSC v.1500 32
> bit (Intel)] on win32
> Type "help", "copyright", "credits" or "license" for more information.
> >>> import apsw
> >>> db = apsw.Connection('')
> >>> db.execute('select ?, ?10;', ('one', 'ten')).fetchone()
> Traceback (most recent call last):
>   File "<stdin>", line 1, in <module>
>   File "C:\python\lib\site-packages\Local\newapsw.py", line 39, in execute
>     return self.cursor().execute(*args, **kwargs)
> apsw.BindingsError: Incorrect number of bindings supplied.  The current
> statement uses 10 and there are 2 supplied.  Current offset is 0
> >>>
>
> The statement required 10 parameters and only 2 were supplied.  ERROR!
>
> Internally it uses sqlite3_bind_parameter_count to find out how many
> parameters need binding and requires that the number of "parameters" passed
> match the number of parameters "expected" when binding positionally.
>
> >>> db.execute('select :1, :10;', {'2': 'one', '10': 'ten'}).fetchone()
> Row(_0=None, _1=u'ten')
> >>> db.execute('select :1, :10;', {'1': 'one', '10': 'ten'}).fetchone()
> Row(_0=u'one', _1=u'ten')
> >>>
>
> When binding by name, it looks up the names in the provided dictionary and
> binds those it finds.
>
> You can also bind named parameters to a positional list (in which case it
> is the programmers job to keep track of what they are doing) since a named
> parameter is merely syntactic sugar on top of positional parameters:
>
> >>> db.execute('select :1, :10;', ('one', 'two')).fetchone()
> Row(_0=u'one', _1=u'two')
> >>>
>
>
>
> It is not very difficult.  You call sqlite3_bind_parameter_count.  Then if
> you are binding positionally you make sure there are sufficient positional
> parameters provided to bind them all.  It you are binding by name, you
> cycle through the parameters, get the name, and then bind the given named
> parameter to that parameter.  apsw chooses to ignore missing items when
> binding by name -- sounds like you simply want to ERROR instead ...
>
> The only issue I can see is that when requesting the name of an positional
> parameter that has no name it returns null rather than the positional name,
> however, this is pretty easy to work around in pretty much any programming
> language ... that is if sqlite3_parameter_name(stmt, x) return null then
> the name is a ? followed by x in decimal (sprintf("?%d", x) or thereabouts
> having appropriate accomodations for the language syntax and buffer safety,
> etc)
>
> However, you cannot bind to positional parameters by name:
>
> >>> db.execute('select ?1, ?10;', {'2': 'one', '10': 'ten'}).fetchone()
> Traceback (most recent call last):
>   File "<stdin>", line 1, in <module>
>   File "C:\python\lib\site-packages\Local\newapsw.py", line 39, in execute
>     return self.cursor().execute(*args, **kwargs)
> apsw.BindingsError: Binding 1 has no name, but you supplied a dict (which
> only has names).
>
>
> *the builtin python sqlite3 wrapper does the same thing I expect (though I
> have never actually looked) but it is somewhat braindead otherwise, so I
> don't use it and haven't bothered to test what it does in these
> circumstances.  Though my expectation is that it would behave somewhat
> similarly.
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-----Original Message-----
> >From: sqlite-users [mailto:sqlite-users-
> >[hidden email]] On Behalf Of test user
> >Sent: Monday, 22 July, 2019 06:36
> >To: SQLite mailing list
> >Subject: Re: [sqlite] [EXTERNAL] Determining valid statement
> >placeholders.
> >
> >I understand the problem, but I want my library to be able to detect
> >the
> >problem programatically.
> >
> >Currently if a user mixes index-based and key-based placeholders, the
> >only
> >thing a library using SQLite can do us run the query with unbound
> >placeholders set to null (the SQLite default).
> >
> >Id like the ability to instead throw an error in this case.
> >
> >
> >I think SQLite internally knows how many placeholders are in the
> >query at
> >parse time.
> >
> >My question is how can I get the data via the API, or if it would be
> >considered to add a function to get this data?
> >
> >
> >On Mon, Jul 22, 2019 at 12:29 PM Keith Medcalf <[hidden email]>
> >wrote:
> >
> >>
> >> On Monday, 22 July, 2019 04:34, Enzo <[hidden email]>
> >wrote:
> >>
> >> >It is not the same information.
> >>
> >> >I want to be able to determine "has the user bound all placeholder
> >> >values with data?".
> >>
> >> >The user provides this as input:
> >>
> >> >query="SELECT ?, ?10"
> >> >data={"0": "data-a", "10": "data-b"}
> >>
> >> >Note: This IS valid, as they have provided all data for
> >placeholders.
> >>
> >> >Using the APIs you mentioned:
> >>
> >> >count=10
> >> >name(10) = "?10"
> >>
> >> >So according to the API, the indexes [1, 2, 3, 4, 5, 6, 7, 8, 9,
> >10]
> >> >are placeholders in the query.
> >>
> >> >As you can see, only indexes [1, 10] are valid placeholders in the
> >> >query.
> >>
> >> >So, as far as I can tell, it is not possible to get this from the
> >> >API:
> >>
> >> >query="SELECT ?, ?10"
> >> >valid_placeholder_indexes=[1, 10]
> >>
> >> >It is only possible to get this:
> >>
> >> >query="SELECT ?, ?10"
> >> >maybe_placeholder_indexes=[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
> >>
> >> The fundamental issue is that you are confusing POSITIONAL
> >parameters with
> >> NAMED parameters.  ?nnn where nnn is numeric describe POSITIONAL
> >> parameters, so in the query SELECT ?, ?10 you are using positional
> >> parameter 1 and positional parameter 10.  That implies that there
> >are 10
> >> positional parameters.  The fact that you are only using those
> >positional
> >> parameters sparsely (only using positional parameter 1 and
> >positional
> >> parameter 10) does not mean that you are using two parameters, it
> >means
> >> that you are using 10, but only referencing 2 of them.
> >>
> >> Said another way, you are declaring a function that looks like
> >this:
> >>
> >> int stmt(a, b, c, d, e, f, g, h, i, j)
> >>    return a + j
> >>
> >> and expecting to be able to call it as
> >>
> >> z = stmt(1, 6)
> >>
> >> expecting some magic to know that the second parameter is really
> >parameter
> >> 10.
> >>
> >> https://www.sqlite.org/lang_expr.html#varparam
> >>
> >> There may be many positional parameters (like 999 in the default
> >build)
> >> and sqlite3_bind_parameter_count returns the "index" of the
> >greatest
> >> parameter number used in the statement.  Having created 10
> >parameters
> >> sqlite has absolutely no clue that you happen to be using only
> >parameter 1
> >> and parameter 10.  If you only needed 2 parameters you should have
> >only
> >> created 2, not 10.  That is what NAMED parameters are for.
> >>
> >> If you change from using positional (?) parameters to using named
> >(:) or
> >> (@) what happens?
> >>
> >> select :1, :10; should only create 2 parameters named :1 and :10
> >...
> >>
> >> --
> >> The fact that there's a Highway to Hell but only a Stairway to
> >Heaven says
> >> a lot about anticipated traffic volume.
> >>
> >>
> >>
> >>
> >> _______________________________________________
> >> sqlite-users mailing list
> >> [hidden email]
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> >users
> >>
> >_______________________________________________
> >sqlite-users mailing list
> >[hidden email]
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users