Using CGI params with a MYSQL query

Ruby
1

I am feeding CGI params to my program for testing purposes from the
keyboard, then using this value to build a MYSQL query. The value is
coming in correctly, however, it seems to be stored in such a way that I
cannot use it in my query.

I have been searching an explanation of how the CGI params are stored
and work, but no luck.

The problem I am having is when using #{myparamvariable}, the MYSQL
query fails it seems because the query is seeing [" "] (brackets) around
the variable.

I am grabbing the CGI param as follows:

myparamvariable=params[‘mycgiparam’]

my query is as follows:

result= dbh.query ("
SELECT *
FROM mytable
WHERE myfield= #{myparamvariable}
")

How can I use this param variable in my query?

Thanks

2

On Wed, Feb 23, 2011 at 4:21 PM, Doug Al [email protected] wrote:

I am feeding CGI params to my program for testing purposes from the
keyboard, then using this value to build a MYSQL query.

The problem I am having is when using #{myparamvariable}, the MYSQL
query fails it seems because the query is seeing [" "] (brackets) around
the variable.

I am grabbing the CGI param as follows:

myparamvariable=params[‘mycgiparam’]

What happens if you log that value (or break into debug) immediately
after?
And what does the query in the MySQL log look like?

my query is as follows:

result= dbh.query ("
SELECT *
FROM mytable
WHERE myfield= #{myparamvariable}
")

Auwe! – http://xkcd.com/327/ – c.f. “prepared statements” :slight_smile:

3

I can use puts to show the value to the screen and it displays
correctly.

I will have to check into the MYSQL log to see what it is there.

MYSQL returns something like this to my screen

in `query’: You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near ‘[“4352221213”]’ at line 3 (Mysql::Error)

4

On Wed, Feb 23, 2011 at 5:19 PM, Doug Al [email protected] wrote:

I can use puts to show the value to the screen and it displays
correctly.

mmmm.

MYSQL returns something like this to my screen

in `query’: You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near ‘[“4352221213”]’ at line 3 (Mysql::Error)

What does myparamvariable.inspect return? That error message
looks like myparamvariable is an Array (one element, but regardless).

5

myparamvariable.inspect returns “[“4352221213”]”

6

Brian, Thanks for your reply.

I am now getting the value assigned to my variable correctly, however,
my query is still returning an error. Not sure what I am missing?

The query is:

result = dbh.query("
SELECT *
FROM mytable
WHERE myfield = ?", myparamvariable
)

And the error returned is:

in query': wrong number of arguments(2 for 1) (ArgumentError) from ./dp.rb:93:in’

Thanks for your help.

7

Doug Al wrote in post #983651:

And the error returned is:

in query': wrong number of arguments(2 for 1) (ArgumentError) from ./dp.rb:93:in’

Oh OK, maybe ‘query’ doesn’t support this. Follow the examples in the
kitebird article, using do/execute etc. (Or you can write your own
helper function which does it)

8

Thanks Brian,

I believe that I just have the MYSQL module installed, not the DBI.

Do you recommend installing DBI also? Does this add more functionality
or what is the difference?

Thanks

9

Doug Al wrote in post #983470:

The problem I am having is when using #{myparamvariable}, the MYSQL
query fails it seems because the query is seeing [" "] (brackets) around
the variable.

That’s because params[‘foo’] is an Array, because CGI allows multiple
instances of the parameter with the same name, e.g.

/myprog.cgi?foo=bar&foo=baz

Use:
myparamvariable = params[‘mycgiparam’][0]
or
myparamvariable = params[‘mycgiparam’].first

my query is as follows:

result= dbh.query ("
SELECT *
FROM mytable
WHERE myfield= #{myparamvariable}
")

Arghh!! If you do that, you are creating a huge security hole. Google
for “SQL injection attacks”, then see this:

However, ruby-dbi provides you with a simple solution:
http://www.kitebird.com/articles/ruby-dbi.html#TOC_8

dbh.query(“SELECT * FROM mytable WHERE myfield=?”, myparamvariable)

Always, always, always use this form for constructing queries.

Regards,

Brian.

10

Sorry, my mistake; I saw “dbh” and drew the wrong conclusion.

The low-level mysql API doesn’t have this convenience feature. You need
to use Mysql.quote. e.g.

“insert into foo (bar) values ‘#{Mysql.quote(str)}’”

Although DBI works, I wouldn’t recommend it for new application. It’s a
stale project and has very little care and attention these days.

I’d say most people are using one of these:

  • ActiveRecord
  • DataMapper
  • Sequel

These all work at a much higher level, and handle quoting for you
(amongst many other things). However, if you really have to write your
application as as a CGI you may find the startup overhead is too high,
especially with ActiveRecord. With CGI you have to fire up a new ruby
interpreter and load in all the libraries you need, for every single
incoming HTTP request; ActiveRecord is pretty huge and this can add one
second or more to the request processing.

This isn’t a problem when using any persistent framework - these days
this normally means anything written on top of Rack, either running its
own standalone webserver (webrick/mongrel/thin/unicorn/rainbows!), or
inside Apache using Phusion Passenger. You start the app once, then it
sits there processing requests one after the other.

Regards,

Brian.