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 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



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

Reply via email to