I've been looking at the OTL library for C++ database access. I'm unsure of whether the query I pass in is converted to a parameterized query for the underlying database, or if it's basically just concatenating all the arguments into one big string and passing the query to the database that way. I see that the query you pass in to it can include type information for the arguments, but what happens between then and the query hitting the database, I can't tell.
views:
79answers:
2
+1
A:
The documentation talks all about bind variables. I assume that the library is rewriting your query, but it's probably just changing the format of the bind variables into the format your DBMS expects, and then binding the values to the bind variables.
Ken Bloom
2010-06-30 14:15:29
That's what I would hope, but I've read the docs a few times now, and I'm just not sure. It's a tossup to me whether OTL is simply using string-based placeholders to concat the values I pass in and translate to the correct SQL dialect for XYZ DBMS, or if it's translating to the correct dialect AND the correct way of defining a parameterized query for XYZ DBMS.
pheadbaq
2010-06-30 14:46:43
@pheadbaq: Certainly, they wrote a library that's safe from SQL injection attacks either way.
Ken Bloom
2010-06-30 15:35:30
@Ken not trying to be a pain, but I don't see how translating to the correct DBMS dialect automatically means injection safety.If I hand OTL a string and it passes WHERE fname = 'bob' to the DMBS as opposed to WHERE fname = @f_name, then I need to protect against injection myself.
pheadbaq
2010-06-30 16:49:46
Sent a message to the library author, I'll post a response if he answers back.
pheadbaq
2010-06-30 17:25:12
Posted the response I got from the author. I'm happy to say you were right, Ken. :) I just didn't want to go on an assumption by myself or others, but now we know.
pheadbaq
2010-06-30 18:48:19
@pheadbaq: In general, I would assume that a DB layer that supports bind variables is actually binding the variables at the driver level. However, if a DB layer is emulating bind variables (interpolating them into the query itself, and not just passing it on to the driver), then I would assume that it knows how to escape the strings properly, so as to avoid an injection attack.
Ken Bloom
2010-06-30 20:12:03
+1
A:
OTL author's response to my e-mail:
OTL passes queries with placeholders into the DB API layers. The naming conventions for actual bind variables are different for different DB types. Say, for Oracle,
SELECT * FROM staff WHERE fname=:f_name<char[20]>
will be translated into:
SELECT * FROM staff WHERE fname=:f_name
plus a bunch of host variable bind calls.
For MS SQL Server, or DB2, the same SELECT would look like this:
SELECT * FROM staff WHERE fname=?
It's described in the manual that you can't have a placeholder with the same name more than once for MS SQL, DB2. SQL statements with placeholder / bind variables are relatively expensive to create, so if you instantiate an parameterized SQL via an otl_stream, it makes sense to reuse the stream as much as you can.
If you have more questions, or suggestions on how I can improve the OTL manual, feel free to email me.
Cheers, Sergei
pheadbaq wrote:
Hi, I've been evaluating C++ DB libraries recently to use as a base for an ORM library I wish to build, and have been gravitating more and more towards the OTL. It looks very nice by the way, and seems like it would meet most of the needs I have. I just have one lingering question that I can't seem to clarify by reading the docs. Does OTL pass a parameterized query on to the underlying DBMS, or is it concatenating the arguments and query I pass to the OTL stream into a single string and hand that to the DBMS?
In other words, if I hand OTL this MSSQL query, along with with the string "Bob" as the bind variable:
SELECT * FROM staff WHERE fname = :f_name<char[20]>
Does the OTL parser produce this:
SELECT * FROM staff WHERE fname = 'Bob'
Or this:
SELECT * FROM staff WHERE fname = @f_name
along with my string as a parameter
I've posted this same question to StackOverflow.com if you care to respond there: http://stackoverflow.com/questions/3149974/is-c-otl-sql-database-library-using-parameterized-queries-under-the-hood-or-st
Thank you for your time
pheadbaq
2010-06-30 18:20:21