easier way to eval a string from file?

Question

I would like to store a mySQL query in a file. I plan on having to string replace parts of it with variables from my program.

I played around with the 'eval' method in ruby, and it works, but it feels a little clumsy.

Using irb I did the following.

>> val = 7
=> 7
>> myQuery = "select * from t where t.val = \#{val}" #escaped hash simulates reading it from file
=> "select * from t where t.val = \#{val}"
>> myQuery = eval "\"#{myQuery}\""
=> "select * from t where t.val = 7"

As you can see it works! But to make it work I had to wrap the 'myQuery' variable in escaped quotes, and the whole thing looks a little messy.

Is there an easier way?

Answer 1

You can use ERB templates instead - read them from the files and interpolate the variables (convert <%= something %> tags into the actual values).

Here's the official doc, it's quite complete and straightforward.

Answer 2

You can use printf like syntax for string replacement

"123 %s 456" % 23 # => "123 23 456"

This only works if your program knows in advance which variables to use.

Answer 3

Could you use parametrized queries?

I don't know off hand how to do so in ruby, but basically it involves marking your SQL statement with commands that SQL recognizes are replaces with parameters that are sent in addition to your statement.

This link might help: http://sqlite-ruby.rubyforge.org/sqlite3/faq.html#538670816

Answer 4

Generally, you should not use string interpolation to build SQL queries. Doing so will leave you open to SQL injection attacks, in which someone supplies input that has a closing quote character, followed by another query. For instance, using your example:

>> val = '7; DROP TABLE users;'
=> "7; DROP TABLE users;"
>> myQuery = "select * from t where t.val = \#{val}"
=> "select * from t where t.val = \#{val}"
>> eval "\"#{myQuery}\""
=> "select * from t where t.val = 7; DROP TABLE users;"

Even without malicious input, you could simply accidentally execute code that you weren't intending to, if for instance someone included quote marks in their input.

It is also generally a good idea to avoid using eval unless absolutely necessary; it makes it possible that if you have a bug in your program, someone could execute arbitrary code by getting it passed to eval, and it makes code less maintainable since some of your source code will be loaded from places other than your regular source tree.

So, how do you do this instead? Database APIs generally include a prepare command, which can prepare to execute an SQL statement. Within that statement, you can include ? characters, which represent parameters that can be substituted within that statement. You can then call execute on the statement, passing in values for those parameters, and they will be executed safely, with no way for someone to get an arbitrary piece of SQL executed.

Here's how it would work in your example. This is assuming you are using this MySQL/Ruby module; if you are using a different one, it will probably have a similar interface, though it may not be exactly the same.

>> val = 7
>> db = Mysql.new(hostname, username, password, databasename)
>> query = db.prepare("select * from t where t.val = ?")
>> query.execute(val)