SQLITE3 strings in where clauses seem confused - 【StackMirror】|ruby|sqlite|sqlite3

I'm wondering if anyone has any clarification on the difference between the following statements using sqlite3 gem with ruby 1.9.x:

@db.execute("INSERT INTO table(a,b,c) VALUES (?,?,?)", 
some_int, other_int, some_string)

and

@db.execute("INSERT INTO table(a,b,c) VALUES (#{some_int},"+ 
+"#{some_int}, #{some_string})")

My problem is: When I use the first method for insertion, I can't query for the "c" column using the following statement:

SELECT * FROM table WHERE c='some magic value'

I can use this:

"SELECT * FROM table WHERE c=?", "some magic value"

but what I really want to use is

"SELECT * FROM table WHERE c IN ('#{options.join("','")}')"

And this doesn't work with the type of inserts.

Does anyone know what the difference is at the database level that is preventing the IN from working properly?

2012-04-05 21:17
by user632657

While not exactly being an answer to your question, you should definitely familiarise yourself with the subject of SQL injection - Jan 2012-04-05 21:36

What does your "some magic value" really look like and what does "I can't query for the "c" column" mean? I'm guessing that you're havinga problem with an embedded single quote and only need to know where to use <code>SQLite3::Database.quote</code> - mu is too short 2012-04-05 22:54

I know about the issue of DB hijacking-but this is a script associated with the command line on the user's data. They can do whatever they want with it: - user632657 2012-04-06 17:38

None of the actual strings have quotes in them. They are filenames-they do include paths-but I've determined it's not a "/" that needs to be escaped. Literally, it's the difference in inserting via the ?,? syntax or inserting them using simple embedded strings - user632657 2012-04-06 17:46

I figured this out quite a while ago, but forgot to come back and point it out, in case someone finds this question at another time.

The difference turns out to be blobs. Apparently when you use the first form above (the substitution method using (?,?)) SQLite3 uses blogs to enter the data. However, if you construct an ordinary SQL statement, it's inserted as a regular string and the two aren't equivalent.

2012-09-18 20:52
by user632657

Insert is not possible to row query but row query used in get data that time this one working.

SQLite in you used in mobile app that time not work bat this row query you write in SQLite Browse in that work

2012-04-06 06:44
by Android