A couple of questions about prepared statements

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

A couple of questions about prepared statements

Tim Streater-3
Suppose I have an SQL statement like this:

select somecol from mytable where myid=3 and badtext="usertext" and goodtext="somegoodtext"

Suppose further that the values of myid and goodtext are generated by reliably me whereas badtext is supplied externally. If I want to use a prepared statement here, is it OK to generate the myid and goodtext parts using the usual string methods from my host language, leaving only badtext as a bound variable, so that my prepared statement looks like this:

select somecol from mytable where myid=3 and badtext=? and goodtext="somegoodtext"

That would simplify my life.

My other question relates to when the database is actually touched. Is it the case that statement preparation and variable binding do not affect the database itself and it's only when the prepared statement is actually executed that the database is touched and might generate an SQLITE_BUSY response?

Thanks.


--
Cheers  --  Tim
_______________________________________________
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: A couple of questions about prepared statements

J. King-3
On 2018-10-19 10:43:21, "Tim Streater" <[hidden email]> wrote:

>Suppose I have an SQL statement like this:
>
>select somecol from mytable where myid=3 and badtext="usertext" and
>goodtext="somegoodtext"
>
>Suppose further that the values of myid and goodtext are generated by
>reliably me whereas badtext is supplied externally. If I want to use a
>prepared statement here, is it OK to generate the myid and goodtext
>parts using the usual string methods from my host language, leaving
>only badtext as a bound variable, so that my prepared statement looks
>like this:
>
>select somecol from mytable where myid=3 and badtext=? and
>goodtext="somegoodtext"
>
>That would simplify my life.
It might complicate your life later. If your "safe" data gets mixed with
unsafe data somewhere that you're not aware of (or it changes in a later
version of your application), you're opening yourself up to stealth
bugs. Binding everything reduces guesswork and long-term maintenance.

Also, statements have a maximum size: if your embedded information is
large (or later becomes large), you can run into problems there, too.

Note, too, that the correct way to quote string is with single-quote
characters. SQLite will accept double-quotes when it's unambiguous, but
it's a good habit to get out of, for the ambiguous cases.

>
>My other question relates to when the database is actually touched. Is
>it the case that statement preparation and variable binding do not
>affect the database itself and it's only when the prepared statement is
>actually executed that the database is touched and might generate an
>SQLITE_BUSY response?
>
When the transaction is committed. If you've not explicitly begun a
transaction, then when the statement is executed, yes.

--
J. King

_______________________________________________
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: A couple of questions about prepared statements

Simon Slavin-3
In reply to this post by Tim Streater-3
On 19 Oct 2018, at 3:43pm, Tim Streater <[hidden email]> wrote:

> is it OK to generate the myid and goodtext parts using the usual string methods from my host language, leaving only badtext as a bound variable, so that my prepared statement looks like this:
>
> select somecol from mytable where myid=3 and badtext=? and goodtext="somegoodtext"

That will work fine.  Just a note that your string quoting characters are wrong (this may have been done by your email client).  They should both be apostrophes:

    select somecol from mytable
        where myid=3 and badtext=? and goodtext='somegoodtext'

Presumably you will
    CREATE INDEX t_tgb ON mytable (myid,goodtext,badtext)

> My other question relates to when the database is actually touched. Is it the case that statement preparation and variable binding do not affect the database itself and it's only when the prepared statement is actually executed that the database is touched and might generate an SQLITE_BUSY response?

The database needs to be read, but not written, during _prepare_v2().  It won't be written until _step().  Depending on your journal mode, it's possible that _prepare() will be blocked because another connection is making a change.

However, proper use of _timeout() will mean you will not care about either of the above.

Simon.
_______________________________________________
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: A couple of questions about prepared statements

Tim Streater-3
On 19 Oct 2018, at 17:33, Simon Slavin <[hidden email]> wrote:

> On 19 Oct 2018, at 3:43pm, Tim Streater <[hidden email]> wrote:
>
>>  is it OK to generate the myid and goodtext parts using the usual string
>> methods from my host language, leaving only badtext as a bound variable, so
>> that my prepared statement looks like this:
>>
>>  select somecol from mytable where myid=3 and badtext=? and
>> goodtext="somegoodtext"
>
> That will work fine.

Good.

> Just a note that your string quoting characters are wrong (this
> may have been done by your email client).  They should both be
> apostrophes:
>
>     select somecol from mytable
>         where myid=3 and badtext=? and goodtext='somegoodtext'

No, that was my fault. Not awake enough yet.

> Presumably you will
>     CREATE INDEX t_tgb ON mytable (myid,goodtext,badtext)
>
>>  My other question relates to when the database is actually touched. Is it
>> the case that statement preparation and variable binding do not affect the
>> database itself and it's only when the prepared statement is actually
>> executed that the database is touched and might generate an SQLITE_BUSY
>> response?
>
> The database needs to be read, but not written, during _prepare_v2().  It
> won't be written until _step().  Depending on your journal mode, it's possible
> that _prepare() will be blocked because another connection is making a change.
>
> However, proper use of _timeout() will mean you will not care about either of
> the above.

OK - I'll bear all that in mind - thanks.



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