I use this code to double quote any query for a mysql database.

$a = $dbh->quote("String from a web form");

$dbh->prepare(SELECT * FROM tab WHERE a=$a);

It's possible to use something like this:

$dbh->prepare(qq{SELECT * FROM tab WHERE a=$a})

It seems not to work !

Thanks

What do you get back?   Nothing at all?   Have you checked the errstr?    

I can see noting obviously wrong;  personally, I wouldn't use $a as that's a special variable used in sort, and I would use a different delimiter to { and } in my qq.  I don't see that either of those two changes will effect your specific problem though.

Did a quick sanity check .... following code worked for me.  You may be able to spot something different to what you've done??

Code:

#!/usr/bin/perl use DBI; $db_handle = DBI -> connect('DBI:mysql:test:dhansak:3306',                'trainee','abc123', {RaiseError => 1})                or die ("connection: $DBI::errstr\n"); $a = $db_handle->quote("ia"); $getkey = $db_handle -> prepare       (qq{SELECT * FROM arrivals where near RLIKE $a}); $getkey -> execute; while (@row = $getkey->fetchrow) {        print "@row\n";        }

Thanks Graham,

but what's happen if i don't use $dbh->quote() ?

Are there alternative ways to prevent mysql injection like addslashes() in php?
(eg. malicious use of singol quote from a web form to retrieve password from a database)

Regards

Ah - I understand the question better now - you're looking to find a way in Perl to prevent special characters in the search string creating a problem, and you're looking to do so without having to use the quote function in the DBI module ...

Firstly, I would encourage you to use quote as you did in your first example - qq is just another way or writing a double quoted string and all it does is prevent you from having to put a \ in front of the " character if it needs to appear as a literal in the string.  You've made a comparison to "addslashes" in PHP - good -  PHP's addslashes and the DBI's quote fill the same role; the difference in that addslashes is part of the main PHP distribution, but quote is part of a module in Perl.   When you think about it this makes sense, as releases of Perl are much less frequent than releaeses of PHP, and also the philosophy of PHP is to include much more in the coire distribution.

Having recommended you use the quote function,  you could use \Q and \E within your double quoted string to force the addition of extra backslashes within an area of that string.  Bear in mind that this has been in Perl for a very long time, and will add more \-es than you really need:

Code:

[localhost:~/jan03] graham% cat dave $string = qq!This is a "string of text" which shouldn't contain backslashes\n!; $string2 = qq!This's a "\Q"string of text"\E" which should contain backslashes\n!; print $string; print $string2; [localhost:~/jan03] graham% perl dave This is a "string of text" which shouldn't contain backslashes This's a "\"string\ of\ text\"" which should contain backslashes [localhost:~/jan03] graham%

Thanks Graham,

I'll definitely use quote function

Regards
Dave

---