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