ruby variable inside sql statement

Question

I am trying to use a ruby variable inside an sql statement. The following code works and deletes the second record of the templates table. How do i replace this number with my user defined variable "deleteid"?

deleteid = gets.chomp
$db.execute %q{DELETE FROM templates
WHERE id = 2}

Answer 1

You can use string interpolation:

$db.execute %{DELETE FROM templates WHERE id = #{deleteid}}

$db.execute %Q{DELETE FROM templates WHERE id = #{deleteid}}

UPDATE

User can pass arbitrary string. Using deleteid directly can be dangerous. As @muistooshort commented, you should escape the deleteid.

Consult your db driver's documentation for methods that accepts parameter and escape the parameter (or prepare method).

For example, if you use sqlite3-ruby, you can use Database#query, which will escape for you.

$db.prepare(%q{DELETE FROM templates WHERE id = ?}, [deleteid])

in pg, use Connection#exec_params:

$db.exec_params(%q{DELETE FROM templates WHERE id = $1}, [deleteid])