How do I join the elements in the array while still keeping the double quote?
I am reading the book Rebuilding Rails. In a small ORM chapter, it uses sqlite3 stones to communicate with a sqlite database. my_table database structure
create table my_table (
id INTEGER PRIMARY KEY,
posted INTEGER,
title VARCHAR(30),
body VARCHAR(32000));
and the code that inserts into my_table in the .rb file:
DB.execute <<-SQL
INSERT INTO #{table} (#{keys.join ","})
VALUES (#{vals.join ","});
SQL
# vals=>["1", "It happend!", "It did!"]
But the sql statement it is addressing would be as follows:
"INSERT INTO my_table (posted,title,body)\n VALUES (1,It happend!,It did!);\n"
This will result in a syntax error due to missing double quotation with "It happened!" and "It was!"
and I check the doc finding that Array # join is returning a string created by converting each element of the array to a string. Therefore, the double quote elements in the array will be converted to string and lose the double quote. And throw sql syntax error.
How to solve this?
Any help would be appreciated! Thank!
source to share
You don't have to do things like this yourself. Unfortunately the mysql2
gem (which I assume you are using) does not support prepared statements, this is how you should do it; but there are several other gems you can use to add functionality.
One mysql-cs-bind
gem that is very simple and just adds it to mysql2
:
client.xquery(<<-SQL, vals)
INSERT INTO #{table} (#{keys.join ","})
VALUES (#{keys.map { "?" }.join(",")});
SQL
Another is the use of a more general gem, such as sequel
one that gives you a lot of functionality across a variety of databases, not just MySQL.
The reason you shouldn't be doing it yourself is because
- Problem solved
- It's easy to make a mistake.
- Bobby Tables can visit your site.
If you absolutely need 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 what format MySQL wants its date / time values in, so may need to be configured)
EDIT: SQLite3 fortunately provides prepared statements and placeholders.
DB.execute <<-SQL, *vals
INSERT INTO #{table} (#{keys.join ","})
VALUES (#{keys.map { "?" }.join(",")});
SQL
EDIT: silly with map
. Thank you Jordan.
source to share
You should avoid using string interpolation ( #{...}
) when building SQL queries. Apart from problems like the ones you are experiencing, this is a great way to open up SQL injection attacks.
You should use parameter binding instead. This allows the database itself to take care of sanitizing and quoting your values correctly and safely, so you don't have to worry about that.
In your case, it will 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 this a bit:
DB.execute <<-SQL, vals INSERT INTO my_table (posted, title, body) VALUES (?, ?, ?); SQL
When the request is executed, the placeholders ?
will be replaced with the corresponding values, sanitized and quoted in the array you supply as the second argument.
You may have noticed by now that this is not entirely equivalent to your code, because I have hardcoded the table names and column names. A limitation of SQLite is that you cannot do parameter binding for such identifiers. What to do then? If you are getting column names from an untrusted source (like a web request from an end user) and therefore cannot simply program them into your query, you will 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 will want to do something like this for table_name
if it comes from an unreliable source.
Once you've checked the 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
PS If there are spaces, quotes, or any other special characters in the table or column names, you will need to escape and quote them. This means to avoid any double quotes by preceding them with another double quote ( "
becomes ""
) and then surrounding the whole with double quotes. A helper method like this could do it:
def escape_and_quote_identifier(str)
sprintf('"%s"', str.gsub(/"/, '""'))
end
Then you 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
source to share