How to join elements in an array while still keep the double quotation?

Question

I am reading the Rebuilding Rails book. In the little ORM chapter, it use sqlite3 gem to communicate with sqlite database. the my_table database structure

create table my_table (
 id INTEGER PRIMARY KEY,
 posted INTEGER,
 title VARCHAR(30),
 body VARCHAR(32000));

and the code that insert into my_table in .rb file is:

DB.execute <<-SQL
    INSERT INTO #{table} (#{keys.join ","})
    VALUES (#{vals.join ","});
    SQL
# vals=>["1", "It happend!", "It did!"]

But the sql statement it turn to will be this:

 "INSERT INTO my_table (posted,title,body)\n     VALUES (1,It happend!,It did!);\n"

It will raise a syntax error because of the miss of double quotation with "It happend!" and "It did!"

and i check the document finding that Array#join returns a string created by converting each element of the array to a string. Therefore the double quotation elements in the array will be converted to string and lose the double quotation. And cause that sql syntax error.

How to solve this?

Any help will be appreciate! Thanks!

Answer 1

Things like this, you shouldn't do yourself. Unfortunately, mysql2 gem (that I believe you are using) does not support prepared statements, which is how you should be doing this; but there are several other gems you can use to add the functionality.

One is mysql-cs-bind gem, which is very simple and just adds this to mysql2:

client.xquery(<<-SQL, vals)
    INSERT INTO #{table} (#{keys.join ","})
    VALUES (#{keys.map { "?" }.join(",")});
    SQL

Another is to use a more general gem like sequel which gives you a lot of functionality in a variety of databases, not just MySQL.

The reason why you shouldn't do it yourself is because

• It is a solved problem
• It is easy to make a mistake
• Bobby Tables might visit your site.

If you absolutely have to do it yourself:

db.execute <<-SQL
    INSERT INTO #{table} (#{keys.join ","})
    VALUES (#{
      vals.map { |val|
        case val
        when String
          "'#{mysql.escape(val)}'"
        when Date, Time, DateTime
          "'#{val}'"
        else
          val
        end
      }.join(', ')
    });
    SQL

(Not sure in what format MySQL wants their date/time values, so tweaking might be necessary)

EDIT: SQLite3 fortunately does provide prepared statements and placeholders.

DB.execute <<-SQL, *vals
    INSERT INTO #{table} (#{keys.join ","})
    VALUES (#{keys.map { "?" }.join(",")});
    SQL

EDIT: being stupid with map. Thanks, Jordan.

Answer 2

You should avoid using string interpolation (#{...}) when constructing SQL queries. In addition to issues like the one you're experiencing, it's a great way to open yourself up to SQL injection attacks.

Instead, you should use parameter binding. This lets the database itself take care of correctly and securely sanitizing and quoting your values so you don't have to worry about it.

In your case it would look like this:

vals = [ "1", "It happened!", "It did!" ]
query = <<SQL
  INSERT INTO my_table (posted, title, body)
  VALUES (?, ?, ?);
SQL

DB.execute(query, vals)

You can shorten it a bit to this:

DB.execute <<-SQL, vals
  INSERT INTO my_table (posted, title, body)
  VALUES (?, ?, ?);
SQL

When the query is executed, the ? placeholders will be replaced with the corresponding values—sanitized and quoted—in the array you supply as the second argument.

You've probably already noticed that this isn't exactly equivalent to your code, because I've hard-coded the table name and column names. A limitation of SQLite is that you can't do parameter binding for identifiers like those. What to do, then? If you're getting column names from an untrusted source (like a web request from an end user) and so can't just hard-code them in your query, you'll have to compromise. Probably the best thing you can do is have a whitelist:

column_whitelist = %w[ posted title body ]

unless keys.all? {|key| column_whitelist.include?(key) }
  raise "Invalid column name '#{key}'!"
end

You'll want to do something similar for table_name if it's coming from an untrusted source, too.

Once you've screened your table and column names, you can safely use them in your query:

column_names = keys.join(", ")
placeholders = keys.map { "?" }.join(", ")

DB.execute <<-SQL, vals
  INSERT INTO #{table_name} (#{column_names})
  VALUES (#{placeholders});
SQL

P.S. If your table or column names have spaces, quotation marks, or any other special characters, you'll need to escape and quote them. That means escaping any double-quotes by preceding them with another double-quote (" becomes "") and then surrounding the whole thing with double-quotes. A small helper method like this would do it:

def escape_and_quote_identifier(str)
  sprintf('"%s"', str.gsub(/"/, '""'))
end

Then you would want to apply it to your table and column names:

table_name = escape_and_quote_identifier(table_name)
column_names = keys.map {|key| escape_and_quote_identifier(key) }
                 .join(", ")
placeholders = keys.map { "?" }.join(", ")

DB.execute <<-SQL, vals
  INSERT INTO #{table_name} (#{column_names})
  VALUES (#{placeholders});
SQL