Passing an cgi variable to a sql query

[lievemario]

I have made a perl script witch gets information out of a database,
transfers it into an xml-file file and than I parse this to a html
document using xsl.
The problem is dat the html doc needs a parameter which can be found in
the xml file.

So I get a user variable as input (e.g..http://../..cgi?person_id=12042)
And I need that variable in my sql-statement, but when I do the following

my $id = param('id');
...
select p.id, p.name, p.first_name from person p where p.id = $id;

I get an error:
CGI Error
The specified CGI application misbehaved by not returning a complete set of
HTTP headers. The headers it did return are:


DBD::mysql::st execute failed: 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 '' at line 10 at C:/Perl/site/lib/XML/Generator/DBI.pm
line 91.




is there a solution for this?
Thxs

 

[Tore Aursand]

So I get a user variable as input (e.g..http://../..cgi?person_id=12042)
And I need that variable in my sql-statement, but when I do the
following

my $id = param('id');
...
select p.id, p.name, p.first_name from person p where p.id = $id;

This isn't valid Perl code. How are we supposed to help you if you don't
show us the Real Thing (tm)?

You certainly have an error in your SQL query. Make sure that you're
dealing with it correctly. See the DBI documentation on information on
how to trap errors.

 

[Sherm Pendley]

So I get a user variable as input (e.g..http://../..cgi?person_id=12042)
And I need that variable in my sql-statement, but when I do the following

my $id = param('id');

Why are you getting param 'id', when in the URL it's clearly 'person_id'?

sherm--

 

[Iain Chalmers]

So I get a user variable as input (e.g..http://../..cgi?person_id=12042) ^^^^^^^^^
And I need that variable in my sql-statement, but when I do the following

my $id = param('id');

^^

one of these things is not like the other...

I'm also wondering if you're using CGI.pm there, and that line should
read something like

my $id = $query->param('id') ;

or if you're shooting youself in the foot with a homegrown cgi parameter
parsing routine...

...
select p.id, p.name, p.first_name from person p where p.id = $id;

I get an error:
CGI Error
The specified CGI application misbehaved by not returning a complete set of
HTTP headers. The headers it did return are:


DBD::mysql::st execute failed: 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 '' at line 10 at C:/Perl/site/lib/XML/Generator/DBI.pm
line 91.

is there a solution for this?

Yeah, you need to fix the SQL syntax. Unfortunately, you haven't shown
us enough of your code to work out how to help you there.

I also wonder if you need to read up on DBI placeholders and SQL
injection attacks - what would happen if I crafted a web request that
sent:

http://../..cgi?person_id=12042;DROP TABLE person;

(with appropriate uri escaping of course...)

big

 

[Sherm Pendley]

or if you're shooting youself in the foot with a homegrown cgi parameter
parsing routine...

Not necessarily. The OO interface is not required to use CGI.pm - you
can import its functions and use them procedurally if you want.

I also wonder if you need to read up on DBI placeholders and SQL
injection attacks

Excellent advice.

sherm--

 

[Alan Mead]

[stuff I didn't entirely follow]

So I get a user variable as input (e.g..http://../..cgi?person_id=12042)
And I need that variable in my sql-statement, but when I do the
following

my $id = param('id');
...
select p.id, p.name, p.first_name from person p where p.id = $id;

I get an error:
CGI Error
The specified CGI application misbehaved by not returning a complete set
of HTTP headers. The headers it did return are:

DBD::mysql::st execute failed: 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 '' at line 10 at
C:/Perl/site/lib/XML/Generator/DBI.pm line 91.

Adding to the other good advice posted, I think you're (1) not handling
errors very well here and (2) focusing in the wrong place.

A good discipline is to make a small test program to show the error. Or
execute the SQL manually yourself. If you cannot replicate the error with
a little test program, then it's highly likely that you have some bug in
your code that is the root cause.

I'd bet you lunch that $id eq '' or somesuch garbage and/or your SQL is
malformed (should it be "p.id='$id'"?) and that's what's causing the
exception in DBI.pm. I've found thousands (maybe millions) of errors in MY
code for every one error in CPAN modules. This is good news! Since it's
invariably my error, I have complete control over fixing it.

Also, this is a placeholder:

my $sql = "select p.id, p.name, p.first_name from person p where p.id=?";
....
$dbh->execute($id);

because otherwise some jerk is going to make a URL like

http://../1;delete * from p

-Alan