Using CGI params with a MYSQL query

[Doug Al]

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

 

[Hassan Schroeder]

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"

 

[Doug Al]

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)

 

[Hassan Schroeder]

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).

 

[Doug Al]

myparamvariable.inspect returns "[\"4352221213\"]"

 

[Brian Candler]

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:
http://xkcd.com/327/

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.

 

[Doug Al]

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 `<main>'

Thanks for your help.

 

[Brian Candler]

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 `<main>'

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)

 

[Doug Al]

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

 

[Brian Candler]

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.