Is there any better approach to pass variables safely instead of prepared statement?

Question

prepared statement is a very good approach for passing variables to the query with high security and efficiency. So all fine. Just there is a small thing which sometimes makes me uncomfortable.

Actually sometimes my queries are made dynamically. And I don't know how many times should I pass a variable. Suppose this query:

UPDATE user
   SET reputation = reputation + (CASE id WHEN :op THEN 2 WHEN :user THEN 15 END)
WHERE id in (:user, :op);

I should pass 2 variables ($user, $op) and I have to bind each one them twice:

$sth->bindValue(":op", $op, PDO::PARAM_INT);
$sth->bindValue(":user", $user, PDO::PARAM_INT);
$sth->bindValue(":user", $user, PDO::PARAM_INT);
$sth->bindValue(":op", $op, PDO::PARAM_INT);

Well sometimes that query will be like this:

UPDATE user
   SET reputation = reputation + (CASE id WHEN :op THEN 2 WHEN :user THEN 15 END)
WHERE id in (:user, :op),

       fee = fee + 
         (CASE id WHEN :op   THEN (SELECT SUM(op_val) FROM money WHERE id = :post_id)
                  WHEN :user THEN (SELECT SUM(user_val) FROM money WHERE id = :post_id)
          END)
WHERE id in (:user, :op);

For query above, I should pass 1 more variable ($post_id). In other word 4 more bind value:

$sth->bindValue(":op", $op, PDO::PARAM_INT);
$sth->bindValue(":user", $user, PDO::PARAM_INT);
$sth->bindValue(":op", $op, PDO::PARAM_INT);
$sth->bindValue(":post_id", $post_id, PDO::PARAM_INT);
$sth->bindValue(":user", $user, PDO::PARAM_INT);
$sth->bindValue(":post_id", $post_id, PDO::PARAM_INT);
$sth->bindValue(":user", $user, PDO::PARAM_INT);
$sth->bindValue(":op", $op, PDO::PARAM_INT);

See? That's hard for me to pass variables to a dynamic query. I mean I have to pass one variable several times. Well is there any other approach to validate a variable instead of prepared statement?

That's poitnless. You don't have to bind the SAME value to the SAME placeholder more than once. You're doing the equivalent of walking into a room, turning on the light, then hammering on the switch some more to make sure it's on. Just bind each param ONCE. — Marc B, Jun 03 '16 at 19:18
@MarcB If I don't bind them multiple times, it throws me a fatal error: **'SQLSTATE[HY093]: Invalid parameter number'** — Martin AJ, Jun 03 '16 at 19:22

score 3 · Accepted Answer · answered Jun 03 '16 at 19:31

In general one can either:

Define some user variables that are then used by subsequent queries in the same session:

$set = $pdo->prepare('SET @op = :op, @user = :user, @post = :post');
$set->bindValue('op'  , $op  , PDO::PARAM_INT);
$set->bindValue('user', $user, PDO::PARAM_INT);
$set->bindValue('post', $post, PDO::PARAM_INT);
$set->execute();

$sth = $pdo->query('
  UPDATE user
  SET    reputation = reputation + CASE id
                        WHEN @op   THEN 2
                        WHEN @user THEN 15
                      END,
         fee        = fee + CASE id
                        WHEN @op   THEN (SELECT SUM(op_val)   FROM money WHERE id = @post)
                        WHEN @user THEN (SELECT SUM(user_val) FROM money WHERE id = @post)
                      END
  WHERE  id in (@user, @op)
');

Create a materialised table that contains your variables, which you join to your query:

$sth = $pdo->prepare('
  UPDATE user
    JOIN (SELECT :op AS op, :user AS user, :post AS post) AS variables
  SET    reputation = reputation + CASE id
                        WHEN variables.op   THEN 2
                        WHEN variables.user THEN 15
                      END,
         fee        = fee + CASE id
                        WHEN variables.op   THEN (SELECT SUM(op_val)   FROM money WHERE id = variables.post)
                        WHEN variables.user THEN (SELECT SUM(user_val) FROM money WHERE id = variables.post)
                      END
  WHERE  id in (variables.user, variables.op)
');
$sth->bindValue('op'  , $op  , PDO::PARAM_INT);
$sth->bindValue('user', $user, PDO::PARAM_INT);
$sth->bindValue('post', $post, PDO::PARAM_INT);
$sth->execute();

However, in this specific case, one could break the UPDATE into two:

$sth1 = $pdo->prepare('
  UPDATE user
  SET    reputation = reputation + 2,
         fee        = fee + (SELECT SUM(op_val) FROM money WHERE id = :post)
  WHERE  id = :op
');
$sth1->bindValue('op'  , $op  , PDO::PARAM_INT);
$sth1->bindValue('post', $post, PDO::PARAM_INT);

$sth2 = $pdo->query('
  UPDATE user
  SET    reputation = reputation + 15,
         fee        = fee + (SELECT SUM(user_val) FROM money WHERE id = :post)
  WHERE  id = :user
');
$sth2->bindValue('user', $user, PDO::PARAM_INT);
$sth2->bindValue('post', $post, PDO::PARAM_INT);

$sth1->execute();
$sth2->execute();

Thank you +1 .. Just do you know why people tell me *"You don't have to bind the SAME value to the SAME placeholder more than once"*. Is that possible? Because when I try it, it throws an error. — Martin AJ, Jun 03 '16 at 19:38
@Stack: You can only reuse placeholder names if you're using emulation mode. — eggyal, Jun 03 '16 at 19:39
Well I always do this after connecting `$db_con->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);`. So I don't use emulation mode. Am I right? — Martin AJ, Jun 03 '16 at 19:41

Sparky · Answer 2 · 2016-06-03T19:31:33.290

2

You can bind variables within the Execute. You would still prepare the statement. Instead of bindParam() you can insert variables as an array in the execute statement.

$stmt->execute(array(':var1'=>$var1, ':var2'=>$var2));

edited Jun 03 '16 at 19:31

answered Jun 03 '16 at 19:19

Sparky

66
10

Yes, that can be done, and is advisable. But how does it answer his question? – miken32 Jun 03 '16 at 19:23
I've used this technique when the same variable is used in the statement more than once. Because it doesn't count bindParam()s the way it does normally using the Prepare, BindParam, Method. Therefore he wont get The error and still be able to bind his parameters and only bind them once. – Sparky Jun 03 '16 at 19:27
My point was, while you're pointing out a useful technique, you aren't directly answering the question. You should edit your question to do so. Also, asking for clarification from the OP in an answer is a good way to get the answer deleted, so don't do it! – miken32 Jun 03 '16 at 19:31

Is there any better approach to pass variables safely instead of prepared statement?

2 Answers2