From: Jamis Buck <jamis@37signals.com>
Date: 2005-10-20T13:31:50+09:00
Subject: Re: Escaping single quotes in SQL queries

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?

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