Escaping single quotes in SQL queries

[lists]

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

message_num = '1'

message_text = "This won't work"

db = SQLite3:atabase.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

 

[Yohanes Santoso]

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

message_num = '1'

message_text = "This won't work"

db = SQLite3:atabase.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.

 

[Jamis Buck]

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

message_num = '1'

message_text = "This won't work"

db = SQLite3:atabase.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?

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

1. Use SQLite3:atabase.quote:

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

2. Use bind variables:

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

Hope that helps,

- Jamis

 

[lists]

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:atabase.quote:

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

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

-Ryan