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}

Which database driver is in $db? Just throwing unescaped values into SQL is criminally irresponsible. — mu is too short
– mu is too short, Commented Dec 30, 2014 at 2:56

falsetru · Accepted Answer · 2014-12-30 03:15:22Z

3

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])

edited Dec 30, 2014 at 3:15

answered Dec 30, 2014 at 1:54

falsetru

371k69 gold badges769 silver badges659 bronze badges

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

Collectives™ on Stack Overflow

ruby variable inside sql statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related