How do I protect against injection for 2+ $_POST[variables] in mySQL query?

Question

I have prepared a series of queries which work. Now I am in the process of securing these queries against injection. I can do it fine when I have one variable in the query but have not been able to find how to do it for more with trial and error.

This is what I am doing (See $sql and $handle->execute()):

<?php   
$sql = "SELECT FORMAT (z.PriceMultiplier * p.BasicTicketPrice,2) 
AS totalPrice 
FROM Zone z JOIN Production p 
WHERE p.Title = :n AND z.Name = :n";
$handle = $conn->prepare($sql); 
$zone = "$_POST[Zone]";
$prod = "$_POST[Production]";
$handle->execute(array(":n"=> $zone, $prod))    
$conn = null;
$res = $handle->fetchAll(); 
    foreach($res as $row) { 
      echo "<input name='Price' type='text' readonly='readonly' value=£".$row['totalPrice']."><br>";             
    }           
?>

How do I assign the variables $zone and $prod to the statement in handle->execute()?

Thank you in advance.

[edit1: SOLUTION: Use ? instead of :n p.Title = ? AND z.Name = ? and just do $handle->execute(array($zone, $prod))]

[edit2: I do not believe this is a duplicate - the question is not how to prevent an injection attack... it is how to deal with multiple variables in doing so.]

in PDO, named placeholders are unique to the array(s); you need to rename one of them and add to the array — Funk Forty Niner, Dec 01 '16 at 16:52
`$zone = "$_POST[Zone]";` so ugly! If, do `$zone = $_POST['Zone'];` but read about `filter_input` http://php.net/manual/en/function.filter-input.php — JustOnUnderMillions, Dec 01 '16 at 16:54
In your case also use just `?` instead of `:n` `p.Title = ? AND z.Name = ?` and just do `$handle->execute(array($zone, $prod))` — JustOnUnderMillions, Dec 01 '16 at 16:57
@JustOnUnderMillions True, however named placeholders are so much easier to keep track of. — Funk Forty Niner, Dec 01 '16 at 17:00
@JustOnUnderMillions - Did the trick - many thanks. How do I vote-up your comment? Looks like I don't have the feature just yet. — Shooresh, Dec 01 '16 at 17:00
No thing, i do it just for the fun of it and to help people. Not for medals or points. :-) — JustOnUnderMillions, Dec 01 '16 at 17:02
@Fred -ii Right, but then you have to know how PDO Statements are working in detail :-) I can be kinda hart. What was the param type for null values? ;) — JustOnUnderMillions, Dec 01 '16 at 17:05
@ScheurichGolzari You need a reputation of +15 to upvote. http://meta.stackexchange.com/questions/17364/how-does-comment-voting-and-flagging-work You can edit questions, or answer questions to gain reputation. Also from asking well written questions, and accepting answers. http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work — chris85, Dec 01 '16 at 17:06
@JustOnUnderMillions It was for the purpose of highlighting the solution as well as to thank you ;). Anyway, I edited your solution to the text. Thanks again. — Shooresh, Dec 01 '16 at 17:10

How do I protect against injection for 2+ $_POST[variables] in mySQL query?

0 Answers0