Training, Open Source computer languages
PerlPHPPythonMySQLApache / TomcatTclRubyJavaC and C++LinuxCSS 
Search for:
Home Accessibility Courses Diary The Mouth Forum Resources Site Map About Us Contact
Use of qq{ to prevent Mysql injection

Posted by dave (dave), 16 January 2003
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

Posted by admin (Graham Ellis), 16 January 2003
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";
       }


Posted by dave (dave), 17 January 2003
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

Posted by admin (Graham Ellis), 17 January 2003
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%


Posted by dave (dave), 20 January 2003
Thanks Graham,

I'll definitely use quote function

Regards
Dave



This page is a thread posted to the opentalk forum at www.opentalk.org.uk and archived here for reference. To jump to the archive index please follow this link.

You can Add a comment or ranking to this page

© WELL HOUSE CONSULTANTS LTD., 2014: Well House Manor • 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01144 1225 708225 • FAX: 01144 1225 899360 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho