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
For 2023 - we are now fully retired from IT training.
We have made many, many friends over 25 years of teaching about Python, Tcl, Perl, PHP, Lua, Java, C and C++ - and MySQL, Linux and Solaris/SunOS too. Our training notes are now very much out of date, but due to upward compatability most of our examples remain operational and even relevant ad you are welcome to make us if them "as seen" and at your own risk.

Lisa and I (Graham) now live in what was our training centre in Melksham - happy to meet with former delegates here - but do check ahead before coming round. We are far from inactive - rather, enjoying the times that we are retired but still healthy enough in mind and body to be active!

I am also active in many other area and still look after a lot of web sites - you can find an index ((here))
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 !


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??


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)


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:

[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


This page is a thread posted to the opentalk forum at 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., 2023: Well House Manor • 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01144 1225 708225 • FAX: 01144 1225 793803 • EMAIL: • WEB: • SKYPE: wellho