«

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

--
Posted via http://www.ruby-forum.com/.

On Wed, Feb 23, 2011 at 4:21 PM, Doug Al <> 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"

--
Hassan Schroeder ------------------------
twitter: @hassan

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)

--
Posted via http://www.ruby-forum.com/.

On Wed, Feb 23, 2011 at 5:19 PM, Doug Al <> 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).

--
Hassan Schroeder ------------------------
twitter: @hassan

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

--
Posted via http://www.ruby-forum.com/.

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.

--
Posted via http://www.ruby-forum.com/.

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.

--
Posted via http://www.ruby-forum.com/.

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)

--
Posted via http://www.ruby-forum.com/.

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

--
Posted via http://www.ruby-forum.com/.

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.

--
Posted via http://www.ruby-forum.com/.