Mysql / PDO issue

Question

I'm trying to convert my website with PDO so that's it's more secure for sql injections.

I'm having a problem here, for i'm checking if the username isn't already registered:

This is my sql atm:

function isregistered($var,$methode) {
    $check1 = mysql_result(mysql_query("SELECT COUNT(gebruikersnaam) FROM leden_temp WHERE ".$methode."='".$var."'"),0);
    $check2 = mysql_result(mysql_query("SELECT COUNT(id) FROM leden WHERE ".$methode."='".$var."'"),0); 

    $check = $check1 + $check2; 

    if($check == 0) {
        return FALSE; 
    } else {
        return TRUE;
    }
}

And i'm trying convert it to sql on this way as a pdo statement:

$check1 = $dbh->query("SELECT COUNT(gebruikersnaam) FROM leden_temp WHERE ".$methode."='".$var."'');

But i'm a bit lost on how this actually gonna work with PDO ? Can you guys help me a bit ?

Are you mixing mysql and PDO or is there a different php file? — Mihai, Aug 29 '13 at 12:25
I'm converting al my mysql to PDO, PDO database is always included in the config.php file. — Kets, Aug 29 '13 at 12:32

Bjorn Smeets · Accepted Answer · 2013-08-29T12:48:26.440

1

It would be even safer if you choose to use prepared statements.

$check1 =  $db->prepare('SELECT * FROM leden_temp WHERE gebruikersnaam = :var');
$arr1 = array(
    ':var'=>$var
);
$check1->execute($arr1);
$row_count_check1 = $check1->rowCount();



$check2 =  $db->prepare('SELECT * FROM leden WHERE gebruikersnaam = :var');
$arr2 = array(
    ':var'=>$var
);
$check2->execute($arr2);
$row_count_check2 = $check2->rowCount();



$check = $row_count_check1 + $row_count_check2; 

if($check == 0) {
    return FALSE; 
} else {
    return TRUE;
}

This way, SQL injection can be prevented much easier

I think you're using Murfy's login-system, so if you want to use the same query to check if an email-address is allready registered, you need to change 'gebruikersnaam' to 'email'.

edited Aug 29 '13 at 12:48

answered Aug 29 '13 at 12:32

Bjorn Smeets

320
1
8

I'm not using Murphy's login-system, it's the first time i'm hearing from that hehe, but this is a function which i can call on on checking on mail in the temp users wich didn't have activated their account, and the currently users activated – Kets Aug 29 '13 at 12:49
1

Murfy's login-system has the same logic behind it :). You can use the above code and simple change the 'gebruikersnaam' for 'email' if you need to check the email. Ok, you'll have to use the same code twice, but it's safer to use stored procedures than to create string manually and execute those. – Bjorn Smeets Aug 29 '13 at 12:52
Ha apparently you're right about Murfy's login-system, only checked the name was Maarten there. Thanks for the help! – Kets Aug 29 '13 at 12:59
No problem :). I've been using his system for a few years now and I've modified and enhanced quite a few aspects of it. You can always mail me at bjornsmeets@pctandt.com if you need any more help :) – Bjorn Smeets Aug 29 '13 at 13:03
Thanks! I've done it like this now `$check2 = $db->prepare('SELECT * FROM leden WHERE `' . $method . '` = :var');` so i can use the function still for checking email and username! – Kets Aug 29 '13 at 13:04
I have sended you an email ;) – Kets Aug 29 '13 at 13:32

score -1 · Answer 2 · answered Aug 29 '13 at 12:36

Using PDO doesn't automatically make your scripts more secure, but it does allow the use of prepared statements.

Depending on where the values of $var and $method come from, you could still be susceptible to SQL injections so you may need to carefully validate that the input is as expected. Prepared statements cannot be used for column names so that part cannot be parameterised, unfortunately.

This is a snippet for parameterising part of the first query. The same idea can be used for the second query as well.

$stmt = $dbh->prepare("SELECT COUNT(gebruikersnaam) FROM leden_temp WHERE ${methode} = :methode");
$stmt->bindParam(':methode', $var);

Mysql / PDO issue

2 Answers2