Using CGI params with a MYSQL query

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

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:

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)

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

myparamvariable.inspect returns “[“4352221213”]”

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.

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)

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

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.

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.

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs