CODEWITHSUNDEEP
rubysqlite
letitworknow
letitworknow

Reputation: 222

SQLite3 database insert using variables

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.

Upvotes: 0

Views: 1728

Answers (1)

mu is too short
mu is too short

Reputation: 434635

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.

Upvotes: 1

Related Questions