Using variables within a mysql query

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

Replies are listed 'Best First'.
Re: Using variables withing a mysql query by Fletch (Bishop) on May 11, 2008 at 16:46 UTC
Print the string you're sending to the DB before hand and make sure it's what you think it's supposed to be. See also the documentation for the `trace` method in the DBI docs (a value of 2 usually should be sufficiently verbose to show what's being sent to the backend). (And also I think you mean `$orderfield eq "id"`, not `==`; but that's neither here nor there</nit> :) The cake is a lie. The cake is a lie. The cake is a lie.	[reply] [d/l] [select]
Re: Using variables withing a mysql query by wfsp (Abbot) on May 11, 2008 at 15:13 UTC
Is the SQL quoted with single or double quotes?	[reply]
Re^2: Using variables withing a mysql query by sulfericacid (Deacon) on May 11, 2008 at 17:16 UTC
The statement is actually `my $data = qq( mysql_here );` [download] "Age is nothing more than an inaccurate number bestowed upon us at birth as just another means for others to judge and classify us" sulfericacid	[reply] [d/l]
Re^3: Using variables withing a mysql query by psini (Deacon) on May 11, 2008 at 19:43 UTC
This should be equivalent to double quotes. What do you get from a `print $data;` after your statement above? If you get exactly the query (`SELECT id, name, location FROM book WHERE 1 ORDER BY id DESC`) and it doesn't work as expected, perhaps you should post more of the surrounding code...	[reply] [d/l] [select]
Re: Using variables withing a mysql query by psini (Deacon) on May 11, 2008 at 16:31 UTC
If the string is enclosed in double quotes the embedded variable should be replaced whit its value. Did you try a test print of the entire query before submitting it to the DBMS? BTW, I hope you are aware that generated queries are a potential security issue (risks from SQL injection) and should be used with care	[reply]
Re: Using variables within a mysql query by hesco (Deacon) on May 11, 2008 at 20:30 UTC
Try something like this: `my $table = 'book'; my $condition = '1'; my $sort_field = 'id'; my $sort_order = 'DESC"; my $sql = "SELECT id, name, location FROM $table WHERE $condition ORDE +R BY $sort_field $sort_order"; print $sql,"\n"; my $sth = $dbh->prepare($sql); $sth->execute() or warn "Unable to query $table" $dbh->errstr . "\n" . + $!; while (my $row = $sth->fetchrow_hashref()) { print $row->{'id'},"\t",$row->{'name'},"\t",$row->{'location'},"\n"; +; }` [download] Remember: single quotes give you a literal, double quotes interpolates included variables. Also, if this is untrusted user input you are manipulating (say the kind collected at a browser) you would be much safer to use bind variables, by replacing these two lines like so: `my $sql = "SELECT id, name, location FROM $table WHERE ? ORDER BY ? ?" +; . . . $sth->execute($condition,$sort_field,$sort_order);` [download] Note: I've never tried to feed a $sort_order as a bind variable and have no idea if that would work. But a few tests ought to let you know if it will. I do know that the rest of this should work, although I have not tested this code, leaving that to you. -- Hugh if( $lal && $lol ) { $life++; }	[reply] [d/l] [select]
Re^2: Using variables within a mysql query by psini (Deacon) on May 11, 2008 at 21:29 UTC
I really doubt that this could work. From DBI's POD: "With most drivers, placeholders can't be used for any element of a statement that would prevent the database server from validating the statement and creating a query execution plan for it" And if it works, it should be a literal substitution, so probably can't be used to avoid sql injection I, in my code, use plain old `sprintf` to build the query when I need to change table or column names and bind only the scalar values Obviously everything is included with sprintf must be validated if coming from outside the script	[reply] [d/l]


There's more than one way to do things
	PerlMonks