Escaping Strings for Ruby Sqlite Insert

Escaping Strings For Ruby SQLite Insert

Don't do it like that at all, string interpolation and SQL tend to be a bad combination. Use a prepared statement instead and let the driver deal with quoting and escaping:

# Ditch the gsub in prepare_for_insert and...
db  = SQLite3::Database.new('/Users/michael/catalog.db')
ins = db.prepare('insert into catalog (column_name) values (?)')
string.each { |s| ins.execute(s) }

You should replace column_name with the real column name of course; you don't have to specify the column names in an INSERT but you should always do it anyway. If you need to insert more columns then add more placeholders and arguments to ins.execute.

Using prepare and execute should be faster, safer, easier, and it won't make you feel like you're writing PHP in 1999.

Also, you should use the standard CSV parser to parse your tab-separated files, XSV formats aren't much fun to deal with (they're downright evil in fact) and you have better things to do with your time than deal with their nonsense and edge cases and what not.

how to escape a string before insert or update in Ruby

You could do this:

ActiveRecord::Base.send(:sanitize_sql,["select * from my_table where description='%s' and id='%s'","mal'formed", 55], "my_table")

Of course, this means that you have the params separately. Not sure if it will work otherwise, but try it out.

JSON string stored in SqlLite -- how to escape single and double quotes

Your string will cause a ruby syntax error:

syntax error, unexpected tIDENTIFIER, expecting end-of-input
            "message" : "This is a test 'messa'ge "

To create your string, you can use:

1. %q[ ], which is equivalent to single quotes.

2. %Q[ ], which is equivalent to double quotes.

You can use any character as the delimiter, eg.

%q|Dave's head is small.|

%q{Dave's head is small.}

%q=Dave's head is small.=

So you can create your string error free, like this:

your_string =  %q[{
    "request" : [
        {
            "name" : "test",
            "number" : "8",
            "data" : {
                "message" : "This is a test 'messa'ge "
            }
        }
    ],

    "data1" : {
        "data2" : 1,
        "data3" : "Hello"
    },

    "message1" : "tes't example"
    }]

\' (backslash, single quote) in a Ruby string

The reason puts works is that it shows what is actually in the string. The bare console is showing the string escaped. Try this on the console:

"Joana d\\'Arc".size

You will get back 12. If both backslashes were there you should get 13.

Hauleth's answer should work.

Inserting with multiple parameters gives SQLite3::RangeException

I think your problem lies with trying to encapsulate the parameter indicators (?) in quotes. This is unnecessary as the driver will wrap the string parameter in quotes and perform any additional escaping before executing the query. When I removed the quotes you example queries executed fine.

It looks like your superfluous quotations are escaping the parameter indicators somehow and the driver isn't seeing them.

Use this:

statement = db.prepare("insert into IntegrationLogin (Username, Password, ProjectID) values (?, ?, 1)")

You were making this harder than it needed to be. Don't worry, this kind of parameter binding completely prevents SQL injection, even without the extra quotes.

SQLite in Ruby: Prepared statement cutting UTF-8 input

(We tried in an IRC session)
Entering the code for him resulted in:

irb(main):004:0> db.query "INSERT INTO test(key, value) VALUES(?, ?)", "foo", "bar\U+FFC3\U+FFA6\U+FFC3\U+FFB8\U+FFC3\U+FFA5"

Since "æ" is "\u00e6" and not "\uffc3" it was evident that the data got corrupted on entry.
Readline::VERSION revealed that his ruby was built against EditLine instead of Readline. Editline is OS X' broken Readline replacement.

Replacing editline with readline should solve this issue.

Edit:

Using escape sequences instead of literal UTF-8 would work too.

---

Related Topics