PDO using quotes in prepared statements to call mysql procedure?

Question

i have to run the following MYSQL procedure call in the following format

CALL inTable(table_name,'<csv of columns>','csv of values');

example to run with prepared statement in php

CALL inTable(users,'name,email',' ? , ? ');

the procedure prepares a mysql query which is used to verify if set of comma separated values in the 3rd argument is present in corresponding comma separated column names in 2nd argument.

If i use the single quotes then the PDO prepared statements leave the ? place holder as is, but it is important for me to use the single quotes as without them the parameter number for procedure becomes incorrect.

this is what my procedure prepares which gets the ? placeholders as is;

SELECT COUNT(*) AS take FROM `users` WHERE (`username`,`email') IN (('?','?'));

using the following php code

$s = self::$handle->prepare("CALL inTable('users','phone,email',' ?,? );");
$s->bindParam(1,$phone, PDO::PARAM_STR);
$s->bindParam(2,$email, PDO::PARAM_STR);
$s->execute();
$s->fetchAll();

should i post the code for mysql procedure which i am using ?

tableExists verifies if a table name exists in db isCommand checks through regexp if modification commands exist in argument bothe the above have 'r' as an OUT argument.

DELIMITER $$
DROP PROCEDURE IF EXISTS inTable $$
CREATE PROCEDURE inTable(t VARCHAR(64),col VARCHAR(1024), val VARCHAR(1024))
BEGIN
    DECLARE r INT DEFAULT NULL;
    DECLARE c VARCHAR(256) DEFAULT NULL;
    DECLARE v VARCHAR(256) DEFAULT NULL;
    DECLARE cl VARCHAR(1024) DEFAULT '';
    DECLARE vl VARCHAR(1024) DEFAULT '';
    DECLARE s TEXT DEFAULT 'SELECT COUNT(*) AS take FROM ';

    CALL tableExists(t,r);

    SET col = TRIM(col);
    SET val = TRIM(val);

    IF r = 1 THEN
        SET r = 0;
        colmn: LOOP
            IF LENGTH(col) = 0 OR col IS NULL OR LENGTH(val) = 0 OR val IS NULL THEN
                LEAVE colmn;
            END IF;

            SET c = SUBSTRING_INDEX(col,',',1);
            SET v = SUBSTRING_INDEX(val,',',1);

            CALL isCommand(c,r);
            IF r = 1 THEN
                LEAVE colmn;
            END IF;
            CALL isCommand(v,r);
            IF r = 1 THEN
                LEAVE colmn;
            END IF;

            IF r = 0 THEN
                SET cl = CONCAT(cl,'`',REPLACE(c,' ',''),'`,');
                SET vl = CONCAT(vl,'\'',TRIM(v),'\',');
                SET col = INSERT(col,1,LENGTH(c) + 1,'');
                SET val = INSERT(val,1,LENGTH(v) + 1,'');               
            END IF;
        END LOOP;
    END IF;

    IF r = 0 THEN
        SET cl = CONCAT('(',INSERT(cl,LENGTH(cl),1,''),')');
        SET vl = CONCAT('((',INSERT(vl,LENGTH(vl),1,''),'))');
        SET @s = CONCAT(s,'`',t,'` WHERE ',cl,' IN ',vl,';');
        SELECT @s;
        PREPARE s FROM @s;
        EXECUTE s;
        DEALLOCATE PREPARE s;
    END IF;
END $$
DELIMITER ;

Why you are using whis toilsome and prone to SQL injection approach? Why not to run a regular query? — Your Common Sense, Apr 12 '20 at 16:11
i do as the boss man commands, i did try to remove possibilites of sql injections — amt, Apr 12 '20 at 16:24

Devon Bessemer · Accepted Answer · 2020-04-12T15:24:06.867

0

You can't bind multiple parameters to a single parameter. That would defeat the level of protection parameter binding provides. Therefore, you only have one parameter in your procedure available, but you're telling your prepare statement to accept two more parameters with ?, ?.

You shouldn't need separate variables for your binding. Just put them together as comma separated values in PHP:

$binding = "$phone,$email";
$s = self::$handle->prepare("CALL inTable('users','phone,email', ?);");
$s->bindParam(1, $binding, PDO::PARAM_STR);

edited Apr 12 '20 at 15:24

answered Apr 12 '20 at 15:15

Devon Bessemer

34,461
9
69
95

the last parameter to the procedure is a comma separated argument, soi can use variable no of columns and values to check – amt Apr 12 '20 at 15:18
It's still just a single argument or parameter and that's all that parameter binding will allow for.. – Devon Bessemer Apr 12 '20 at 15:20
will it not be possible to do multiple bindings ? – amt Apr 12 '20 at 15:20
You can only have one binding for each parameter. – Devon Bessemer Apr 12 '20 at 15:21
is there a link which i can follow to know more about this topic? – amt Apr 12 '20 at 15:24
I'm sure there is. You can try googling mysql parameter binding or the PHP manual. – Devon Bessemer Apr 12 '20 at 15:25

PDO using quotes in prepared statements to call mysql procedure?

1 Answers1