SQL queries without injection vulnerability (DBXTalk)
I'd like to run some ad-hoc queries against my database without opening up a security vulnerability. I don't see any direct way to use parameterized queries from DBXTalk. I'd expect to see something like #executeStatement:withArguments: but I don't. In fact, I don't even see odbx_escape in the image anywhere. What's the trick here? Surely GlorpDBX et. al. are not gluing together bits of SQL with bits of user-supplied text and running it unescaped. What am I missing? http://www.linuxnetworks.de/doc/index.php/OpenDBX/C_API/Usage#Executing_stat... Thanks for your time, â Daniel Lyons
Daniel, I cannot help with opendbx, but I can comment on the way Glorp generates SQL. You typically write blocks of code that look like a normal Smalltalk block, but they have limitations to what you can write in there. These blocks are evaluated and get converted to SQL by some kind of Visitor. The SQL string is then concatenated out of fragments. BUT: Glorp does not have an API to provide SQL fragments to include in a query. So there is not an easy way to inject SQL into a Glorp genaretd query. What you are asking for is not related to Glorp, but to the underlying DB interface, DBXTalk in your case. If you want to provide the user with the ability to provide SQL clauses and construct queries out of that, I guess you are on your own. So the question is: do your users write SQL fragments or are you thinking of a tool to construct queries by example? Do you provide objects to fill in some example data or do your users know the tables? The right solution for you depends on what exactly you have in mind. For our project we use Glorp and provide forms to enter string fragments or number ranges or dates etc. Then we use these in Glorp's blocks. This way the structure of the query is provided as Smalltalk-code and only user-provided data for the right-side operand of comparisons is provided by the user. It is extremely hard to misuse this because the query String will always be fixed and so fragments that include SQL will always lead to SQL errors. I am still learning about all the powerful things Glorp can do to cunstruct subqueries, outer joins, having clauses and stuff, but every time I find something I am excited ;-). HTH Joachim Am 14.01.14 07:06, schrieb Daniel Lyons:
I'd like to run some ad-hoc queries against my database without opening up a security vulnerability. I don't see any direct way to use parameterized queries from DBXTalk. I'd expect to see something like #executeStatement:withArguments: but I don't. In fact, I don't even see odbx_escape in the image anywhere. What's the trick here? Surely GlorpDBX et. al. are not gluing together bits of SQL with bits of user-supplied text and running it unescaped. What am I missing?
http://www.linuxnetworks.de/doc/index.php/OpenDBX/C_API/Usage#Executing_stat...
Thanks for your time,
â Daniel Lyons
-- ----------------------------------------------------------------------- Objektfabrik Joachim Tuchel mailto:jtuchel@objektfabrik.de Fliederweg 1 http://www.objektfabrik.de D-71640 Ludwigsburg http://joachimtuchel.wordpress.com Telefon: +49 7141 56 10 86 0 Fax: +49 7141 56 10 86 1
Joachim, On Jan 14, 2014, at 12:24 AM, jtuchel@objektfabrik.de wrote:
I cannot help with opendbx, but I can comment on the way Glorp generates SQL. You typically write blocks of code that look like a normal Smalltalk block, but they have limitations to what you can write in there. These blocks are evaluated and get converted to SQL by some kind of Visitor. The SQL string is then concatenated out of fragments.
BUT: Glorp does not have an API to provide SQL fragments to include in a query. So there is not an easy way to inject SQL into a Glorp genaretd query. What you are asking for is not related to Glorp, but to the underlying DB interface, DBXTalk in your case.
Interesting. So, I guess I'm wondering, how does Glorp escape the strings?
If you want to provide the user with the ability to provide SQL clauses and construct queries out of that, I guess you are on your own.
So the question is: do your users write SQL fragments or are you thinking of a tool to construct queries by example?
Oh, I'm afraid I haven't been very clear. The "user" supplying the query is me, the programmer. You see, I love SQL and I love relational databases. I probably lost most of the list with that sentence. :) I'm porting this program from PHP. It's a US Census browser, if you're curious: http://census.7gf.org . Anyway, it's basically one table and a huge pile of indexes and some set queries. Each page there is just a different query or two. I got through the front page and the about page with Seaside and PostgresV3 and it works great, but now I need to handle a query with the state as a parameter. In PHP (and JDBC, and Python's DBAPI), I can say "SELECT * FROM places WHERE state = ? ORDER BY population DESC" and then supply an array with the state in it, which fills in the '?' in the query. On a project with a true object model and real behavior on the objects, this approach wouldn't scale up. But in this case, the program is just a nice frontend on a simple one-table database. Glorp is bound to be more effort than it's worth. I only brought it up thinking Glorp must be doing this kind of escaping. It sounds like you're saying it has some other mechanism though, in which there is some kind of visitor that does the escaping, and I won't be able to leverage that.
Do you provide objects to fill in some example data or do your users know the tables? The right solution for you depends on what exactly you have in mind. For our project we use Glorp and provide forms to enter string fragments or number ranges or dates etc. Then we use these in Glorp's blocks. This way the structure of the query is provided as Smalltalk-code and only user-provided data for the right-side operand of comparisons is provided by the user. It is extremely hard to misuse this because the query String will always be fixed and so fragments that include SQL will always lead to SQL errors.
I agree :)
I am still learning about all the powerful things Glorp can do to cunstruct subqueries, outer joins, having clauses and stuff, but every time I find something I am excited ;-).
I have another project I am looking forward to learning Glorp for. Don't worry, I'm sure I'll need a lot of help to get that going! Thanks, â Daniel Lyons
Daniel, Am 14.01.14 08:37, schrieb Daniel Lyons:
Joachim,
On Jan 14, 2014, at 12:24 AM, jtuchel@objektfabrik.de wrote:
I cannot help with opendbx, but I can comment on the way Glorp generates SQL. You typically write blocks of code that look like a normal Smalltalk block, but they have limitations to what you can write in there. These blocks are evaluated and get converted to SQL by some kind of Visitor. The SQL string is then concatenated out of fragments.
BUT: Glorp does not have an API to provide SQL fragments to include in a query. So there is not an easy way to inject SQL into a Glorp genaretd query. What you are asking for is not related to Glorp, but to the underlying DB interface, DBXTalk in your case. Interesting. So, I guess I'm wondering, how does Glorp escape the strings? I don't feel like looking this up for you right now, but my guess would be that it is done in Smalltalk code.
If you want to provide the user with the ability to provide SQL clauses and construct queries out of that, I guess you are on your own.
So the question is: do your users write SQL fragments or are you thinking of a tool to construct queries by example? Oh, I'm afraid I haven't been very clear. The "user" supplying the query is me, the programmer. You see, I love SQL and I love relational databases. I probably lost most of the list with that sentence. :)
Don't we all preach everybody should use the right tool for the job (as long as it's Smalltalk, of course ;-) )?
I'm porting this program from PHP. It's a US Census browser, if you're curious: http://census.7gf.org . Anyway, it's basically one table and a huge pile of indexes and some set queries. Each page there is just a different query or two. I got through the front page and the about page with Seaside and PostgresV3 and it works great, but now I need to handle a query with the state as a parameter. In PHP (and JDBC, and Python's DBAPI), I can say "SELECT * FROM places WHERE state = ? ORDER BY population DESC" and then supply an array with the state in it, which fills in the '?' in the query.
Since I guess you are not talking about stored procedures (which I think are supported in Glorp), I now get what you are asking about. You need something to make sure a parameter to 'SELECT * FROM places WHERE state = %1 ORDER BY population DESC' bindWith: self stateName is correct SQL. This is not so much a problem for Strings, but types like Date and Timestamp... So this is where somebody else would have to jump in for DBXtalk. VA Smalltalk's DB interface Layer has helper methods for this. Glorp uses these.
On a project with a true object model and real behavior on the objects, this approach wouldn't scale up. But in this case, the program is just a nice frontend on a simple one-table database. Glorp is bound to be more effort than it's worth. I only brought it up thinking Glorp must be doing this kind of escaping. It sounds like you're saying it has some other mechanism though, in which there is some kind of visitor that does the escaping, and I won't be able to leverage that. Well, Glorp might help for some more complex structures. I guess the table maps to much more than one class. But if you want to keep things simple, I agree: Glorp might not be the best tool for the job.
Do you provide objects to fill in some example data or do your users know the tables? The right solution for you depends on what exactly you have in mind. For our project we use Glorp and provide forms to enter string fragments or number ranges or dates etc. Then we use these in Glorp's blocks. This way the structure of the query is provided as Smalltalk-code and only user-provided data for the right-side operand of comparisons is provided by the user. It is extremely hard to misuse this because the query String will always be fixed and so fragments that include SQL will always lead to SQL errors. I agree :)
I am still learning about all the powerful things Glorp can do to cunstruct subqueries, outer joins, having clauses and stuff, but every time I find something I am excited ;-). I have another project I am looking forward to learning Glorp for. Don't worry, I'm sure I'll need a lot of help to get that going! Just ask. Did you know there are specialized mailing lists for glorp and dbxtalk? You can find them on forum.world.st.
Joachim -- ----------------------------------------------------------------------- Objektfabrik Joachim Tuchel mailto:jtuchel@objektfabrik.de Fliederweg 1 http://www.objektfabrik.de D-71640 Ludwigsburg http://joachimtuchel.wordpress.com Telefon: +49 7141 56 10 86 0 Fax: +49 7141 56 10 86 1
Did you know there are specialized mailing lists for glorp and dbxtalk? You can find them on forum.world.st.
If you'd rather I ask there I will, but they did not look promising. The DBXTalk site also seems to be gone. â Daniel Lyons
Joachim, On Jan 14, 2014, at 12:54 AM, Daniel Lyons <fusion@storytotell.org> wrote:
Did you know there are specialized mailing lists for glorp and dbxtalk? You can find them on forum.world.st.
If you'd rather I ask there I will, but they did not look promising. The DBXTalk site also seems to be gone.
This puts the emphasis on the wrong thing. I am genuinely not sure what to do. I'm not wedded to DBXTalk, it just seems to be the emphasized way to access a database nowâif this isn't the right path, I'm here to be steered in the right direction. I like PostgresV3's connection pooling, but if it's "legacy" then I'd rather get in with what's going to be used. It looks like Mariano is maintaining PostgresV2, but V3 stalled out in 2010. So, it may be a stretch for here, but I am not sure "should I use your library or not" is going to be on-topic on those mailing lists. If there's a mailing list for PostgresV3, I didn't find it. Thanks again, â Daniel Lyons
On 14 Jan 2014, at 08:54, Daniel Lyons <fusion@storytotell.org> wrote:
Did you know there are specialized mailing lists for glorp and dbxtalk? You can find them on forum.world.st.
If you'd rather I ask there I will, but they did not look promising. The DBXTalk site also seems to be gone.
no its not :) Iâm just changing servers and old .com.ar domain is still not refreshed (and you, accomplishing murphyâs law, just tried to hit the site now :). you can find it at dbxtalk.smallworks.eu anyway⦠no, we did not implemented sql injection defence. Is our understanding that that is better done at pharo level, before calling any dbx function. Esteban
â Daniel Lyons
Esteban Lorenzano writes:
no its not :) Iâm just changing servers and old .com.ar domain is still not refreshed (and you, accomplishing murphyâs law, just tried to hit the site now :). you can find it at dbxtalk.smallworks.eu
Thanks! This makes me feel much better.
anyway⦠no, we did not implemented sql injection defence. Is our understanding that that is better done at pharo level, before calling any dbx function.
I'm sure that if I could replace ' with '' on the way out it would be sufficient. Forgive my ignorance, but what's the simplest way to do that? Database vendors tend to provide a function that does at least that, and OpenDBX provides access to that via their odbx_escape function, but round-tripping through FFI for each component of a string may have undesirable performance ramifications. -- Daniel Lyons
On 14 Jan 2014, at 18:00, Daniel Lyons <fusion@storytotell.org> wrote:
I'm sure that if I could replace ' with '' on the way out it would be sufficient. Forgive my ignorance, but what's the simplest way to do that?
Replace all a's by aa's: | in | in := 'shjgababbaz' readStream. String new: in size streamContents: [ :out | [ in atEnd ] whileFalse: [ in peek = $a ifTrue: [ out nextPut: $a ]. out nextPut: in next ] ]. Sven
I'm porting this program from PHP. It's a US Census browser, if you're curious: http://census.7gf.org . Anyway, it's basically one table and a huge pile of indexes and some set queries. Each page there is just a different query or two. I got through the front page and the about page with Seaside and PostgresV3 and it works great, but now I need to handle a query with the state as a parameter. In PHP (and JDBC, and Python's DBAPI), I can say "SELECT * FROM places WHERE state = ? ORDER BY population DESC" and then supply an array with the state in it, which fills in the '?' in the query.
Hi Daniel, The sentence above could be a huge difference: "SELECT * FROM places WHERE state = ? ORDER BY population DESC" One thing is a prepared statement. That means....the database can compile the query in advance, then reuse it later and then when you execute it all it does is to bind variables to arguments. This is mostly for performance reason. If you want this, no, as far as I remember, openDBX driver does not support prepared statements right now. If you simply mean to write the above in smalltalk code, then that's very easy: 'SELECT * FROM places WHERE state = {1} ORDER BY {2} DESC' format: #('FL' ' population') and that answers 'SELECT * FROM places WHERE state = FL ORDER BY population DESC' but this is only at Smalltalk code, just an API facility. It has nothing to do with prepared statement. The database will directly receive the query. Regarding the SQL injection, yes, we didn't implement the one provided by OpenDBX, but you can give it a try yourself....but you would need to recompile stuff. Also, as mentioned by Joachim, if you use Glorp, you are not likely to write the queries yourself..so at least that helps... but I don't know if Glorp provides something extra for sql injection. You could ask in glorp google group. Cheers, -- Mariano http://marianopeck.wordpress.com
Mariano Martinez Peck writes:
Regarding the SQL injection, yes, we didn't implement the one provided by OpenDBX, but you can give it a try yourself....but you would need to recompile stuff.
I may take a crack at it, but I'm not well-experienced with Smalltalk and certainly not knowledgeable about the FFI facility. If I come up with anything, I will write back.
Also, as mentioned by Joachim, if you use Glorp, you are not likely to write the queries yourself..so at least that helps... but I don't know if Glorp provides something extra for sql injection. You could ask in glorp google group.
Thanks for your help, -- Daniel Lyons
I made the same question a month or so ago. Apparently it is supported by the driver, but not the implementation. GLORP mentions it also support parametrized queries. In the case of Oracle it can be not only a security concern, but also a performance issue (the CPU use can skyrocket when compiling the amount of queries created by an ORM under a heavy load). By now I'm escaping what I can at the image level, because I hadn't time to look deeper into it. But parametrized queries (aka prepared statements) is the way to go. About OpenDBX as a project, I can't say much. I don't even know comparison numbers between going native or using DBX in between. Regards, Esteban A. Maringolo 2014/1/14 Daniel Lyons <fusion@storytotell.org>:
I'd like to run some ad-hoc queries against my database without opening up a security vulnerability. I don't see any direct way to use parameterized queries from DBXTalk. I'd expect to see something like #executeStatement:withArguments: but I don't. In fact, I don't even see odbx_escape in the image anywhere. What's the trick here? Surely GlorpDBX et. al. are not gluing together bits of SQL with bits of user-supplied text and running it unescaped. What am I missing?
http://www.linuxnetworks.de/doc/index.php/OpenDBX/C_API/Usage#Executing_stat...
Thanks for your time,
â Daniel Lyons
participants (6)
-
Daniel Lyons -
Esteban A. Maringolo -
Esteban Lorenzano -
jtuchel＠objektfabrik.de -
Mariano Martinez Peck -
Sven Van Caekenberghe