How to insert Ruby array items into SQLite?

Question

I want to execute my INSERT strings within a Ruby file that has the following array:

names_arr = ["Jack", "Susan", "Peter"]

In the same Ruby file, I've required the SQLite3 gem and have this code:

names_arr.each do |each_name|
  db.execute('INSERT INTO users (name) VALUES ("#{each_name}");')
end

I am expecting this in my "users" table:

id  name
1   Jack
2   Susan
3   Peter

Instead, I'm getting this:

id  name
1   #{each_name}
2   #{each_name}
3   #{each_name}

It's clear I'm not interpolating properly, but I've tried a lot of different things and can't figure out where I'm wrong.

I am using SQLite3 and Ruby.

Try this db.execute("INSERT INTO users (name) VALUES (#{each_name});" — Arup Rakshit
– Arup Rakshit, Commented Oct 6, 2013 at 21:16

mu is too short · Accepted Answer · 2013-10-06 22:14:09Z

2

You have several problems in this code:

names_arr.each do |each_name|
  db.execute('INSERT INTO users (name) VALUES ("#{each_name}");')
end

You're trying to use Ruby's string interpolation inside a single quoted string but string interpolation only works in double quoted strings (and heredocs, %Q{...} strings, regex literals, ...).
Standard SQL uses single quotes for string literals, not double quotes. Some databases let you get away with double quotes but it is a really bad habit to get into.
You shouldn't use string interpolation to build SQL anyway, you're not hacking PHP in 1999 so you should use placeholders and let the database library deal with the quoting.

The SQLite driver's execute understands ? placeholders so you can say this:

names_arr.each do |each_name|
  db.execute('INSERT INTO users (name) VALUES (?)', each_name)
end

answered Oct 6, 2013 at 22:14

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

2 Comments

Thanks, this was most helpful! Also found this link that helped with inserting multiple columns of data with ? placeholders.

FYI, I just learned that another term for placeholders mu is using is "sanitation replacement."