Why does my query work on the command line, but fail in my browser?

chromatic has asked for the wisdom of the Perl Monks concerning the following question:

This is often a quoting issue. For example, your query may look like this:

INSERT member (last, first)
VALUES(NULL, "chromatic");
[download]

and your script like this:

$last = "NULL";
$first = "chromatic";
$query = 
qq {
    INSERT member (last, first)
    VALUES ($last, $first)
};
$rows = $dbh->do($query);
[download]

Printing your $query would reveal that $last and $first are not being quoted.

The solution is to use something like this:

$last = $dbh->quote("NULL");
$first = $dbh->quote("chromatic");
[download]

or this:

$query = 
qq {
    INSERT member (last, first)
    VALUES (?, ?)
};
$rows = $dbh->do($query, undef, $last, $first);
[download]

Originally posted as a Categorized Question.

Comment on Why does my query work on the command line, but fail in my browser? Select or Download Code

Replies are listed 'Best First'.
Re: Why does my query work on the command line, but fail in my browser? by tommyw (Hermit) on Sep 18, 2001 at 18:02 UTC
One possibility (seen all too often) is that your webserver invokes your perl script in a different environment from the one you're using. For example, if you're using Oracle, making sure that ORACLE_HOME is set makes a difference Have a look in your servers log files: with luck it'll pinpoint the problem.	[reply]
Re: Why does my query work on the command line, but fail in my browser? by cavac (Prior) on Jul 15, 2012 at 21:57 UTC
The query `INSERT member (last, first) VALUES(NULL, "chromatic");` [download] also uses double quotes. Most databases either don't accept that, or interpret it in some other way (like PostgreSQL, which uses it to indicate that column name or table name is case sensitive. This is especially problematic when you just copy the whole string from whatever tool you use to test the queries into Perl.	[reply] [d/l]
Re: Why does my query work on the command line, but fail in my browser? by CMonster (Scribe) on Jul 07, 2000 at 22:05 UTC
Whoopsie! Be wary of quoting NULLs. The program listing has $dbh->quote("NULL"); but that gives 'NULL', or the string NULL, rather than the empty value NULL. To get the latter, use $dbh->quote(undef); ...which works just fine if quoting a variable; you get a quoted string when it contains a value, and NULL when it's undef.	[reply]
Re: Why does my query work on the command line, but fail in my browser? by Anonymous Monk on Jan 10, 2002 at 07:00 UTC
set the ORACLE_HOME and ORACLE_SID in your perl script. like: $ENV{'ORACLE_HOME'}='/home/oracle'; $ENV{'ORACLE_SID'}='ora1';	[reply]
Re: My query works fine from the command line, but it fails in my script! by Jonathan (Curate) on Apr 14, 2000 at 17:11 UTC
I find it easier to build up the query string in a series of sprintf statements where the single quotes can be easily embedded :)	[reply]
RE: Answer: My query works fine from the command line, but it fails in my script! by athomason (Curate) on Jun 28, 2000 at 23:42 UTC
This is a common mistake, but don't be tempted to commit it. While it's easier to add your own quotes, you're bound to run into trouble when the string you're quoting has quotes of it's own. While an accidental inclusion of a stray quote will probably only break your script, a malicious user could attempt to modify your query to get information you never intended to be public. Also, quoting mechanisms are different across databases. What works on the platform you're developing with may not work with another when you need to change down the road. The DBI quote method always properly quotes strings by respecting the particular database's conventions for escaping metacharacters and enclosing quoted strings. Placeholders automatically use DBI quotes and make like easier in the long run, so you're much better off using them.	[reply]
Re: Why does my query work on the command line, but fail in my browser? by ralphie (Friar) on Sep 26, 2001 at 16:51 UTC
check to make sure that the userid the script runs under has been granted database access rights. when using apache, the id can be found in httpd.conf under the user/group settings.	[reply]