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}
")
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}
")
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)
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:
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).
···
On Wed, Feb 23, 2011 at 5:19 PM, Doug Al <douga@cachecomm.com> wrote:
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)
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.
