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
