On 2018-10-19 10:43:21, "Tim Streater" <t...@clothears.org.uk> 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
