Stack Stack - 6 months ago 14
MySQL Question

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

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?

Answer

In general one can either:

  1. 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)
    ');
    
  2. 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();