Hi,
I am currently using PDO with MySQL. From what I've read using stored procedures with PDO helps to limit SQL injection since parameters get bound by reference. As an example I use the following function with a PDO wrapper:
Code:
public static function UserLogin($email, $password)
{
$sql = 'CALL user_login(:email, :password)';
// Build the parameters array
$params = array (':email' => $email, ':password' => $password);
// Execute the query and return the results
return = DatabaseHandler::GetRow($sql, $params);
}
And the user_login stored procedure which it calls as follows:
Code:
CREATE PROCEDURE user_login(IN inEmail VARCHAR(100), IN inPassword VARCHAR(30))
BEGIN
SELECT user_id, user_pwd, user_fname, user_lname
FROM tbl_users WHERE user_email = inEmail AND user_pwd = inPassword;
END
The above works fine in if you try to inject something ' or 'a'='a in to both the email and password fields the query will fail.
However I have an instances where I need to build a dynamic query and am having to use prepared statments as follows in my stored procedure - note the below is just an example to illustrate the problem, I am not actually using it for login purposes:
Code:
CREATE PROCEDURE user_login(IN inEmail VARCHAR(100), IN inPassword VARCHAR(30))
BEGIN
SET @statement = 'SELECT user_id, user_pwd, user_fname, user_lname
FROM tbl_users WHERE';
IF (inEmail <> "") THEN
SET @statement = CONCAT(@statement, ' (user_email = ''', inEmail,''') ');
END IF;
IF (inPassword <> "") THEN
SET @statement = CONCAT(@statement, 'AND (user_pwd = ''', inPassword,''') ');
END IF;
PREPARE dynquery FROM @statement;
EXECUTE dynquery;
DEALLOCATE PREPARE dynquery;
END$$
If I inject something like ' or 'a'='a in to both the email and password fields the query will return true.
I am unable to escape these parameters using mysqli_real_escape_string as am using PDO.
Any suggestion on how I can sanitize the data to prevent SQL injections whne using prepared statements as above with PDO?
Regards
dBforums
>
Why is SQL injection possible even with prepared statements
02-14-10, 07:05
Linear Mode
