Re: [sqlite] Exec vs Prepare, step, finalize.

[Michael Falconer]

...as for Stephen, Mr Beal you need to get out more LOL! Little Johnny
Tables indeed. Rub it in, why not? LOL


On 12 August 2016 at 09:38, Michael Falconer <michael.j.falco...@gmail.com>
wrote:

> Thanks all,
>
> must admit to being around db's for years but I never did get my head
> around the whole injection thing, sad but true. Keith summed it up in usual
> succinct fashion which when read by one old hack cause much reddening of
> the facial features. Bugger, says I, that speaks my language and it's
> saying you are a goose! I'm admitting to no more!
>
> Thanks all for opening my eyes, at long last, and excuse me while I grep
> my code for sqlite3_exec()grr...damnetc.
>
>
> On 11 August 2016 at 23:40, Quan Yong Zhai <q...@msn.com> wrote:
>
>> > From: michael.j.falco...@gmail.com
>> > Date: Thu, 11 Aug 2016 15:53:39 +1000
>> > To: sqlite-users@mailinglists.sqlite.org
>> > Subject: Re: [sqlite] Exec vs Prepare, step, finalize.
>> >
>> > I have a self styled routine (similar to the glibc manual example) for
>> > concatenating the strings values that make up the sql statement. It uses
>> > memcpy rather than the built in strcat etc.
>> sqlite3_mprintf  http://www.sqlite.org/c3ref/mprintf.html provide some
>> formattingoptions to defending SQL injection. '%Q' to quote string
>> parameters, '%w' to quote table name or column name..
>> >So what exactly is the issue
>> > with the string building if it does  not include sql derived from user
>> > input? I'm not quite seeing that bit, sorry or the vagueness
>> >
>> > It does however sound like it would just be better to adopt the three
>> step
>> > functions as the preferred method in all cases, which is probably what
>> I'm
>> > trying to come to grips with. I do see the prepare/step/finalize process
>> > with bound parameters etc is very much preferred in most cases, but
>> > wondered if those cases where SQL is application provided were an
>> > exception. I'm leaning towards a no on that now. Thanks for your input
>> and
>> > in advance or any additional insight.
>> >
>>
>> I am not a security expert, but I think the culprit of SQL injection
>> vulnerability in SQLite is not sqlite3_exec(). It's the way  how the SQL
>> command text constructed. if you look into the SQLite source code, there
>> are many places used sqlite3_exec(), and  theparameters are carefully
>> quoted by '%Q', '%q' or '%w'.
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> Regards,
>  Michael.j.Falconer.
>



-- 
Regards,
 Michael.j.Falconer.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Exec vs Prepare, step, finalize.

[Michael Falconer]

Thanks all,

must admit to being around db's for years but I never did get my head
around the whole injection thing, sad but true. Keith summed it up in usual
succinct fashion which when read by one old hack cause much reddening of
the facial features. Bugger, says I, that speaks my language and it's
saying you are a goose! I'm admitting to no more!

Thanks all for opening my eyes, at long last, and excuse me while I grep my
code for sqlite3_exec()grr...damnetc.


On 11 August 2016 at 23:40, Quan Yong Zhai <q...@msn.com> wrote:

> > From: michael.j.falco...@gmail.com
> > Date: Thu, 11 Aug 2016 15:53:39 +1000
> > To: sqlite-users@mailinglists.sqlite.org
> > Subject: Re: [sqlite] Exec vs Prepare, step, finalize.
> >
> > I have a self styled routine (similar to the glibc manual example) for
> > concatenating the strings values that make up the sql statement. It uses
> > memcpy rather than the built in strcat etc.
> sqlite3_mprintf  http://www.sqlite.org/c3ref/mprintf.html provide some
> formattingoptions to defending SQL injection. '%Q' to quote string
> parameters, '%w' to quote table name or column name..
> >So what exactly is the issue
> > with the string building if it does  not include sql derived from user
> > input? I'm not quite seeing that bit, sorry or the vagueness
> >
> > It does however sound like it would just be better to adopt the three
> step
> > functions as the preferred method in all cases, which is probably what
> I'm
> > trying to come to grips with. I do see the prepare/step/finalize process
> > with bound parameters etc is very much preferred in most cases, but
> > wondered if those cases where SQL is application provided were an
> > exception. I'm leaning towards a no on that now. Thanks for your input
> and
> > in advance or any additional insight.
> >
>
> I am not a security expert, but I think the culprit of SQL injection
> vulnerability in SQLite is not sqlite3_exec(). It's the way  how the SQL
> command text constructed. if you look into the SQLite source code, there
> are many places used sqlite3_exec(), and  theparameters are carefully
> quoted by '%Q', '%q' or '%w'.
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Regards,
 Michael.j.Falconer.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Exec vs Prepare, step, finalize.

[Quan Yong Zhai]

> From: michael.j.falco...@gmail.com
> Date: Thu, 11 Aug 2016 15:53:39 +1000
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] Exec vs Prepare, step, finalize.
> 
> I have a self styled routine (similar to the glibc manual example) for
> concatenating the strings values that make up the sql statement. It uses
> memcpy rather than the built in strcat etc.
sqlite3_mprintf  http://www.sqlite.org/c3ref/mprintf.html provide some 
formattingoptions to defending SQL injection. '%Q' to quote string parameters, 
'%w' to quote table name or column name..
>So what exactly is the issue
> with the string building if it does  not include sql derived from user
> input? I'm not quite seeing that bit, sorry or the vagueness
> 
> It does however sound like it would just be better to adopt the three step
> functions as the preferred method in all cases, which is probably what I'm
> trying to come to grips with. I do see the prepare/step/finalize process
> with bound parameters etc is very much preferred in most cases, but
> wondered if those cases where SQL is application provided were an
> exception. I'm leaning towards a no on that now. Thanks for your input and
> in advance or any additional insight.
> 

I am not a security expert, but I think the culprit of SQL injection 
vulnerability in SQLite is not sqlite3_exec(). It's the way  how the SQL 
command text constructed. if you look into the SQLite source code, there are 
many places used sqlite3_exec(), and  theparameters are carefully quoted by 
'%Q', '%q' or '%w'.
  
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Exec vs Prepare, step, finalize.

[Jay Kreibich]

On Aug 11, 2016, at 12:53 AM, Michael Falconer  
wrote:

> Thanks Jay,
> 
> excellent response. I'll ask for clarity on one statement though.
> 
> That’s the basic theory, but even knowing that, most people get it wrong.
>> In short, if you’re using string manipulation functions to build your query
>> string, you’re very very very very likely doing it wrong.
>> 
> 
> I have a self styled routine (similar to the glibc manual example) for
> concatenating the strings values that make up the sql statement. It uses
> memcpy rather than the built in strcat etc. So what exactly is the issue
> with the string building if it does  not include sql derived from user
> input? I'm not quite seeing that bit, sorry or the vagueness.

If the values are truly 100% application derived, with no source or root 
material
from any user-generated inputs, config files, etc., then you’re good… assuming
you still quote correctly, and so forth.

The thing is, that’s rarely the case, and as the program changes and evolves, 
it is
almost inevitable that user-driven values make it into the statements.   Yes, 
that’s a
broad, sweeping, generalization, and you know your specific development needs
best.  But remember that SQL injection attacks are essentially impossible if
you correctly use bound parameters, yet it is also considered one of the top
10 security vectors.  In 99% of cases, they're really easy to avoid, and yet
they’re all over the place.  There is a lesson in caution in that.


> It does however sound like it would just be better to adopt the three step
> functions as the preferred method in all cases, which is probably what I'm
> trying to come to grips with. I do see the prepare/step/finalize process
> with bound parameters etc is very much preferred in most cases, but
> wondered if those cases where SQL is application provided were an
> exception. I'm leaning towards a no on that now. Thanks for your input and
> in advance or any additional insight.

Be aware that bound parameters are for *values* only.  You cannot bind
identifiers, which includes database names and table names.  From what
I gathered in the rest of this discussion, that might be an issue in your case.

 -j

--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Exec vs Prepare, step, finalize.

[Keith Medcalf]

So if a user typed in

authors; drop table importfinancialdata;

for the tablename, what would your software do?

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Michael Falconer
> Sent: Wednesday, 10 August, 2016 23:54
> To: SQLite mailing list
> Subject: Re: [sqlite] Exec vs Prepare, step, finalize.
> 
> Thanks Jay,
> 
> excellent response. I'll ask for clarity on one statement though.
> 
> That’s the basic theory, but even knowing that, most people get it wrong.
> > In short, if you’re using string manipulation functions to build your
> query
> > string, you’re very very very very likely doing it wrong.
> >
> 
> I have a self styled routine (similar to the glibc manual example) for
> concatenating the strings values that make up the sql statement. It uses
> memcpy rather than the built in strcat etc. So what exactly is the issue
> with the string building if it does  not include sql derived from user
> input? I'm not quite seeing that bit, sorry or the vagueness.
> 
> It does however sound like it would just be better to adopt the three step
> functions as the preferred method in all cases, which is probably what I'm
> trying to come to grips with. I do see the prepare/step/finalize process
> with bound parameters etc is very much preferred in most cases, but
> wondered if those cases where SQL is application provided were an
> exception. I'm leaning towards a no on that now. Thanks for your input and
> in advance or any additional insight.
> 
> 
> On 11 August 2016 at 14:32, Jay Kreibich <j...@kreibi.ch> wrote:
> 
> >
> > On Aug 10, 2016, at 9:21 PM, Michael Falconer <
> > michael.j.falco...@gmail.com> wrote:
> >
> > > Hi all,
> > >
> > > just seeking some opinions, and perhaps some dev indications about
> > > deprecation, in relation to the sqlite3_exec facility. I kind of like
> the
> > > callback functionality in certain cases as it is convenient in some
> > > circumstances where the sql injection problem is not an issue.
> > >
> > > Ok I say it is not an issue, but am I right. I am no security expert
> and
> > > have often been surprised at some of the hack techniques used over the
> > > years. The sql injection issue as far as I can tell depends on where
> the
> > > offending sql originates, but don't hesitate to contradict that
> > assumption
> > > if you believe it is wrong.
> >
> > That’s the basic theory, but even knowing that, most people get it
> wrong.
> > In short, if you’re using string manipulation functions to build your
> query
> > string, you’re very very very very likely doing it wrong.
> >
> > > In a scenario where the sql supplied to the callback routine is
> > application
> > > generated or indeed application constant based does the sql injection
> > > threat disappear?
> >
> > Yes.  The threat is when you start to use sprintf() to built your query
> > strings.
> > Even more so if some of those inputs can trace their origin to user
> > generated
> > values.
> >
> > > user supplied sql via arguments, with only database name and table
> name
> > > required from the user. This would appear to be immune to that
> technique
> > or
> > > am I misguided? I'm never certain when it comes to security stuff, I
> hate
> > > it.
> >
> > You are misguided.  The whole idea behind injections is that you can
> alter
> > that
> > database name so that it is much more than a database name.  If you
> can’t
> > understand the whys of it, you can’t defend against it.  And that’s
> > important
> > in this case, since you can’t use bound parameters for database names or
> > table names.  Switching to _prepare() won’t help in this specific case
> > because
> > you have no choice but to build the query from string primitives.
> >
> >
> > Another issue with sqlite_exec() is that all the values are returned as
> > strings.
> > You have no idea what the type of the field is, and if it is a non-
> string
> > value,
> > it is converted to a string before the callback is called.  This can be
> a
> > big issue
> > for many designs.
> >
> > > In a similar vein I noted in an O'Reilly publication it mentioned that
> > the
> > > exec method was semi depracated and should be avoided. I wondered what
> > the
> > > view of the SQLite dev crew was. and if there were any plans in the
> > future
> > > to drop the exec function? In 

Re: [sqlite] Exec vs Prepare, step, finalize.

[Simon Slavin]

On 11 Aug 2016, at 5:32am, Jay Kreibich  wrote:

> In short, if you’re using string manipulation functions to build your query
> string, you’re very very very very likely doing it wrong.

There are situations where it's the best (possibly only) way, though.  If you 
want to offer your users a flexible search/sort ability then there can be too 
many different possibilities to store all the possible queries.  A clearer 
formulation of the above is that when you build your SQL command string, you do 
not build it directly from user input.

So using a bunch of 'IF' statements to build a 'SELECT' string is okay.

Allowing the user to pick a column name from a popup list you populate, and 
concatenating a 'SELECT' string including the one the user picked is okay.

But allowing the user to type a column name and concatenating a 'SELECT' string 
including what the user typed is not.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Exec vs Prepare, step, finalize.

[Clemens Ladisch]

Michael Falconer wrote:
> So what exactly is the issue with the string building if it does not
> include sql derived from user input?

That somebody will change the code later, or use it as a template.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Exec vs Prepare, step, finalize.

[Stephan Beal]

On Thu, Aug 11, 2016 at 7:53 AM, Michael Falconer <
michael.j.falco...@gmail.com> wrote:

> Thanks Jay,
>
> excellent response. I'll ask for clarity on one statement though.
>
> That’s the basic theory, but even knowing that, most people get it wrong.
> > In short, if you’re using string manipulation functions to build your
> query
> > string, you’re very very very very likely doing it wrong.
> >
>
> I have a self styled routine (similar to the glibc manual example) for
> concatenating the strings values that make up the sql statement. It uses
> memcpy rather than the built in strcat etc. So what exactly is the issue
> with the string building if it does  not include sql derived from user
> input? I'm not quite seeing that bit, sorry or the vagueness.
>

This short strip explains SQL injection better than any book, IMO:

https://xkcd.com/327/


It does however sound like it would just be better to adopt the three step
> functions as the preferred method in all cases, which is probably what I'm
> trying to come to grips with. I do see the prepare/step/finalize process
> with bound parameters etc is very much preferred in most cases, but
> wondered if those cases where SQL is application provided were an
> exception. I'm leaning towards a no on that now. Thanks for your input and
> in advance or any additional insight.
>

FWIW, internally, exec() is just a proxy for prepare/step/finalize.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Exec vs Prepare, step, finalize.

[Michael Falconer]

Thanks Jay,

excellent response. I'll ask for clarity on one statement though.

That’s the basic theory, but even knowing that, most people get it wrong.
> In short, if you’re using string manipulation functions to build your query
> string, you’re very very very very likely doing it wrong.
>

I have a self styled routine (similar to the glibc manual example) for
concatenating the strings values that make up the sql statement. It uses
memcpy rather than the built in strcat etc. So what exactly is the issue
with the string building if it does  not include sql derived from user
input? I'm not quite seeing that bit, sorry or the vagueness.

It does however sound like it would just be better to adopt the three step
functions as the preferred method in all cases, which is probably what I'm
trying to come to grips with. I do see the prepare/step/finalize process
with bound parameters etc is very much preferred in most cases, but
wondered if those cases where SQL is application provided were an
exception. I'm leaning towards a no on that now. Thanks for your input and
in advance or any additional insight.


On 11 August 2016 at 14:32, Jay Kreibich  wrote:

>
> On Aug 10, 2016, at 9:21 PM, Michael Falconer <
> michael.j.falco...@gmail.com> wrote:
>
> > Hi all,
> >
> > just seeking some opinions, and perhaps some dev indications about
> > deprecation, in relation to the sqlite3_exec facility. I kind of like the
> > callback functionality in certain cases as it is convenient in some
> > circumstances where the sql injection problem is not an issue.
> >
> > Ok I say it is not an issue, but am I right. I am no security expert and
> > have often been surprised at some of the hack techniques used over the
> > years. The sql injection issue as far as I can tell depends on where the
> > offending sql originates, but don't hesitate to contradict that
> assumption
> > if you believe it is wrong.
>
> That’s the basic theory, but even knowing that, most people get it wrong.
> In short, if you’re using string manipulation functions to build your query
> string, you’re very very very very likely doing it wrong.
>
> > In a scenario where the sql supplied to the callback routine is
> application
> > generated or indeed application constant based does the sql injection
> > threat disappear?
>
> Yes.  The threat is when you start to use sprintf() to built your query
> strings.
> Even more so if some of those inputs can trace their origin to user
> generated
> values.
>
> > user supplied sql via arguments, with only database name and table name
> > required from the user. This would appear to be immune to that technique
> or
> > am I misguided? I'm never certain when it comes to security stuff, I hate
> > it.
>
> You are misguided.  The whole idea behind injections is that you can alter
> that
> database name so that it is much more than a database name.  If you can’t
> understand the whys of it, you can’t defend against it.  And that’s
> important
> in this case, since you can’t use bound parameters for database names or
> table names.  Switching to _prepare() won’t help in this specific case
> because
> you have no choice but to build the query from string primitives.
>
>
> Another issue with sqlite_exec() is that all the values are returned as
> strings.
> You have no idea what the type of the field is, and if it is a non-string
> value,
> it is converted to a string before the callback is called.  This can be a
> big issue
> for many designs.
>
> > In a similar vein I noted in an O'Reilly publication it mentioned that
> the
> > exec method was semi depracated and should be avoided. I wondered what
> the
> > view of the SQLite dev crew was. and if there were any plans in the
> future
> > to drop the exec function? In light of the teams focus on backward
> > compatibility I suspect there are no such plans but I thought I'd ask
> > anyway just to be sure. Thanks in advance for any helpful comments.
>
>   -j  (Author, Using SQLite).
>
>
> --
> Jay A. Kreibich < J A Y @ K R E I B I.C H >
>
> "Intelligence is like underwear: it is important that you have it, but
> showing it to the wrong people has the tendency to make them feel
> uncomfortable." -- Angela Johnson
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Regards,
 Michael.j.Falconer.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Exec vs Prepare, step, finalize.

[Jay Kreibich]

On Aug 10, 2016, at 9:21 PM, Michael Falconer  
wrote:

> Hi all,
> 
> just seeking some opinions, and perhaps some dev indications about
> deprecation, in relation to the sqlite3_exec facility. I kind of like the
> callback functionality in certain cases as it is convenient in some
> circumstances where the sql injection problem is not an issue.
> 
> Ok I say it is not an issue, but am I right. I am no security expert and
> have often been surprised at some of the hack techniques used over the
> years. The sql injection issue as far as I can tell depends on where the
> offending sql originates, but don't hesitate to contradict that assumption
> if you believe it is wrong.

That’s the basic theory, but even knowing that, most people get it wrong.
In short, if you’re using string manipulation functions to build your query
string, you’re very very very very likely doing it wrong.

> In a scenario where the sql supplied to the callback routine is application
> generated or indeed application constant based does the sql injection
> threat disappear?

Yes.  The threat is when you start to use sprintf() to built your query strings.
Even more so if some of those inputs can trace their origin to user generated
values.

> user supplied sql via arguments, with only database name and table name
> required from the user. This would appear to be immune to that technique or
> am I misguided? I'm never certain when it comes to security stuff, I hate
> it.

You are misguided.  The whole idea behind injections is that you can alter that
database name so that it is much more than a database name.  If you can’t
understand the whys of it, you can’t defend against it.  And that’s important
in this case, since you can’t use bound parameters for database names or
table names.  Switching to _prepare() won’t help in this specific case because
you have no choice but to build the query from string primitives.


Another issue with sqlite_exec() is that all the values are returned as strings.
You have no idea what the type of the field is, and if it is a non-string value,
it is converted to a string before the callback is called.  This can be a big 
issue
for many designs.

> In a similar vein I noted in an O'Reilly publication it mentioned that the
> exec method was semi depracated and should be avoided. I wondered what the
> view of the SQLite dev crew was. and if there were any plans in the future
> to drop the exec function? In light of the teams focus on backward
> compatibility I suspect there are no such plans but I thought I'd ask
> anyway just to be sure. Thanks in advance for any helpful comments.

  -j  (Author, Using SQLite).


--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users