variable not passing to sql query

Question

I have a database with just 2 fields. The first is a list of numbers and the second is a status of the numbers. I am trying to have a form that will allow me to mass input the list of numbers and update their status to used. I cannot figure out where I am messing up. Any and all help is much appreciated.

The PHP

$pin=$_POST['pin'];
$xx=explode("\n", $pin);

$sql="UPDATE new_table SET Status = 'used' WHERE number = '". $pin . "'";
foreach($xx as $pin){
    if($pin!=""){

    $sql.="('".trim($pin)."'),";
    }
}
$sql=substr($sql,0,-1);

$mysql=new mysqli('127.0.0.1', '******', '********', '******');
$mysql->query($sql);
$mysql->close();

}

?>

The form

<html>

<body>
<form method="post">
    <label> Enter Sims (1 for each line)</label>
    <br/>
    <textarea rows="10" cols="100" name="pin" ></textarea>
    <br/>
    <input type="submit" value="save" />
</form>
</body>
</html>

What's the problem? Are you getting an error? If so, what error? — StudioTime, Jun 03 '16 at 22:17
I am not getting an error. The database field is just not updating. I have tried a million different ways and cannot get it to update. — gpratt86, Jun 03 '16 at 22:26
What does your `$sql` look like when the query is run? Have you tried echoing it out and running it manually? — StudioTime, Jun 03 '16 at 22:30

score 0 · Answer 1 · edited May 23 '17 at 12:22

Warning: your code, as well as code samples in this answer, are potentially vulnerable to SQL-injection attack.

Assuming that your $_POST['pin'] contains data like this (a single multi-line string separated with \n):

1234
5678
9012

putting it directly into query string will lead to having query:

UPDATE new_table SET Status = 'used' WHERE number = 1234
5678
9012

This will produce a syntax error and hence the query will not be executed.

Understanding your goal, you need to update status for that fields, where its number field is IN one of pins. The correct query should look like:

UPDATE new_table SET Status = 'used' WHERE number IN (1234, 5678, 9012)

To achieve this, you can use implode function. So, to create query similar to above one, you can change your code as following:

$pin = $_POST['pin'];
$xx = implode(",", explode("\r\n", $pin));

$sql = "UPDATE new_table SET Status = 'used' WHERE number IN (". $xx . ")";

$mysql=new mysqli('127.0.0.1', '******', '********', '******');
$mysql->query($sql);
$mysql->close();

nice SQL injection warning, but you went ahead and left it open anyway? — Jeff Puckett, Jun 03 '16 at 22:36
@JeffPuckettII the code provided by me is only used to lead OP to the correct way of solving his issue. It's all upon him whether to filter incoming data or not. — lolbas, Jun 03 '16 at 22:40

variable not passing to sql query

1 Answers1