SQLite3 database insert using variables

Question

I am writing a script that exports results into a SQLite database. I can not get the code to work when I use variables.

Here is an excerpt:

require 'sqlite3'
require 'shodan'

table_name = "#{Date.today.strftime("%B")}#{Time.now.year}_Findings"
db = SQLite3::Database.new "shodan_test.db"
db.execute ("CREATE TABLE #{table_name} (ip string , ports string)")

results = api.host(target)

ip = results["ip_str"].to_s
ports = results["ports"].to_s

db.execute ("insert into #{table_name} (ip, ports) values (#{ip}, #{ports})")

The code fails at the last line. I can remove the variables and the code works. I'm a bit confused since the CREATE TABLE works with a variable.

When you say "can not get the code to work" and "The code fails", we'd expect there would be some sort of status or error message, or at least a more descriptive statement about why it doesn't work. It's really hard to tell what the problem is without those. Please edit your question and add that information in as if you'd originally included it. That helps us help you. — the Tin Man
– the Tin Man, Commented Nov 5, 2015 at 19:50
I'd recommend reading about Sequel. It's much better than writing directly to the SQLite API as an ORM, which Sequel is, abstracts your DBM-centric code into more generic code, making it MUCH easier to move to alternate database-types. — the Tin Man
– the Tin Man, Commented Nov 5, 2015 at 19:52

mu is too short · Accepted Answer · 2015-11-06 05:18:36Z

1

Your problem is that you're not quoting your strings. The result is that SQLite sees things like

insert into ... values (192.168.0.6, whatever is in ports)

and that's not valid SQL.

SQLite3::Database#execute understands placeholders so you should use them:

db.execute("insert into #{table_name} (ip, ports) values (?, ?)", ip, ports)

The placeholders (?) in the query will be replaced with properly quoted and escaped versions of their values. Using placeholders avoids all the usual quoting and injection problems that plague using string interpolation for SQL.

Also note that I've removed the space between execute and (. If you put a space there then Ruby thinks your "method calling parentheses" are actually "expression grouping parentheses" and it will complain about not understanding what those commas are doing. If you're only passing one argument (such as in db.execute ("CREATE ...")) then it won't matter because the parentheses are effectively ignored but it does matter when there are multiple arguments. In any case, o.m (arg) is a bad habit to get into, you should say o.m(arg) or leave the parentheses out.

answered Nov 6, 2015 at 5:18

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dmitry Sokurenko Over a year ago

Exactly, that's how the answer should look like :)

Collectives™ on Stack Overflow

SQLite3 database insert using variables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related