Skip to main content
gamedev.net
🔒 Locked

[web] PHP, Perl, and parameterized SQL

Started by BradDaBug 6 replies 1.4k views
Original Post
BradDaBug
BradDaBug
I'm trying to add some parameterized SQL to a particular site written in Perl. But I discover that all it seems to be doing is escaping quotes, which still leaves me vulnerable to injection attacks, and leaves a bunch of slashes in my database that I have to remove whenever I pull anything out. Yuck. So then I tried PHP. At first it seemed to work, but it turns out it does the same thing, only it escapes single quotes instead of double quotes like Perl did. They can't even agree on what kinds of quotes need to be escaped? Or is this not really a problem with PHP or Perl and instead I should blame MySQL 4? If so, does MySQL 5 support parameterized SQL any better?
I like the DARK layout!
konForce
konForce
The PHP/PDO (or mysqli)/MySQL 4.1+ combo has parameter binding.
Sander
Sander
Quote:
Original post by BradDaBug
They can't even agree on what kinds of quotes need to be escaped?


Perl probabely has a different string parsing syntax from PHP, so the escaping needs to be different as well.

Quote:
Or is this not really a problem with PHP or Perl and instead I should blame MySQL 4?


Sounds like a problem in your website code. You're probabely calling mysql_escape_string() or the perl equivalent somewhere on the variables passed in MySQL strings. This is what you should do for non-parameterized SQL but not for parameters since the escapes will become part of the parameter instead of being parsed in a string.

If it's not that, then have a look at the magic quotes setting. Not sure what the Perl variant of that is.
BradDaBug
BradDaBug
Here's an example of how I'm doing parameters in Perl:
# get the message in questionmy $sql = "select message, title from bdb_table where id =?";my $cursor = $dbh->prepare($sql);# $postid is grabbed from the query string and is supposed to just be some number$cursor->bind_param(1, $postid);$cursor->execute;


And here's some PHP:
$sql = "select uid, moderator from user_table where username = ? and password = ?";$results = $connection->query($sql, array($username, $password));

I'm not calling anything that should be escaping quotes. Also I just looked at the actual DB and it looks like PHP is escaping both " and '.
I like the DARK layout!
Sander
Sander
$connection is a custom class? Perhaps that one calls mysql_escape_string() on all elements of the array you pass it.
BradDaBug
BradDaBug
$connection is just my connection to the database. I get it like this:
$connection =& DB::connect($bdb_connect_string);

DB::connect() is something inside DB.php, which I assume is a standard PHP include.
I like the DARK layout!
BradDaBug
BradDaBug
It turns out that it was magic quotes (at least for PHP)! I finally got it turned off and now it looks like it works the way I want. Thanks!
I like the DARK layout!

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.

Sign in to reply to this topic.