Escaping single quotes in SQL queries

ruby-talk
1

I'm trying to do something like the following with sqlite3:

message_num = '1'

message_text = "This won't work"

db = SQLite3::Database.new('/tmp/test.db')

db.execute( "INSERT INTO table VALUES('#{message_num}', '#{message_text}');" )

The above query fails because the single quote in message_text isn't escaped. In my actual script, message_text is part of a huge hash fed in by another process. I thought about double quoting #{message_text} in the SQL but that chokes if message_text contains double quotes. Any ideas?

Thanks,
Ryan

2

lists <lists@kalama.no-ip.org> writes:

I'm trying to do something like the following with sqlite3:

message_num = '1'

message_text = "This won't work"

db = SQLite3::Database.new('/tmp/test.db')

db.execute( "INSERT INTO table VALUES('#{message_num}', '#
{message_text}');" )

The above query fails because the single quote in message_text isn't
escaped. In my actual script, message_text is part of a huge hash
fed in by another process. I thought about double quoting #
{message_text} in the SQL but that chokes if message_text contains
double quotes. Any ideas?

Thanks,
Ryan

This is prone to sql injection attack. Consider using
placeholders which is meant to relieve you of this mechanical burden.
Look at the doc for Database#execute.

Example:

db.execute('INSERT INTO table VALUES (?,?)',
           message_num, message_text)

YS.

3

There are (at least) two ways to handle this:

1. Use SQLite3::Database.quote:

   message_text = SQLite3::Database.quote("This won't work")

2. Use bind variables:

   db.execute( "INSERT INTO table VALUES (?, ?)", 1, "This won't work" )

Hope that helps,

- Jamis

···

On Oct 19, 2005, at 10:05 PM, lists wrote:

I'm trying to do something like the following with sqlite3:

message_num = '1'

message_text = "This won't work"

db = SQLite3::Database.new('/tmp/test.db')

db.execute( "INSERT INTO table VALUES('#{message_num}', '#{message_text}');" )

The above query fails because the single quote in message_text isn't escaped. In my actual script, message_text is part of a huge hash fed in by another process. I thought about double quoting #{message_text} in the SQL but that chokes if message_text contains double quotes. Any ideas?

4

db.execute('INSERT INTO table VALUES (?,?)',
           message_num, message_text)

2. Use bind variables:

  db.execute( "INSERT INTO table VALUES (?, ?)", 1, "This won't work" )

That did the trick, thanks Yohanes and Jamis!

1. Use SQLite3::Database.quote:

  message_text = SQLite3::Database.quote("This won't work")

I didn't know about that one (obviously) so thanks for passing that along.

-Ryan

···

On Oct 19, 2005, at 11:30 PM, Yohanes Santoso wrote:
On Oct 19, 2005, at 11:31 PM, Jamis Buck wrote: