query is not getting updated

Question

I am trying to insert the input received from a text box in a table. The result I am getting in php page are below: Before entering the input, on page load i get Connected successfullyError: INSERT INTO tested (itemno, item, quantity) VALUES ('1', , )

On entering the input and submitting, I get the result , connected successfully and item is not empty msg but the query is not executed and a row is not getting inserted. Can someone help me?

<!DOCTYPE html>
<html>
<body>
<?php
$servername = "localhost";
$username = "learnphp@localhost";
$password = "password";
$dbname = "my_learnphp";
$result="";
$result1="";
$item = $_POST["item"];
$quantity = $_POST["quantity"];

?>
<form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>" method = "POST">
 Item name: <input type="text" name="item"><br>
 Quantity : <input type="number" name="quantity"><br>
<input type="submit" name="upd" value="Insert"/>
<input type="submit" name="item_update" value="Update"/>
</form>
<?php
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
 if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);} 
echo "Connected successfully";
$sql = "INSERT INTO tested (itemno, item, quantity) VALUES ('1', $item, $quantity)";
  if (!empty($item)) {
 echo "item is not empty";
 $result = $conn->query($sql);
 if($result){
 echo $_POST["item"];
echo $_POST["quantity"];
    echo "New record created successfully";
    }
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?> 
</body>
</html>

listen to jeroen!. and about your error: in the query, put `$item`and `$quantity` in single quotes. — kscherrer, Oct 03 '17 at 06:50
phibao37, thanks working. I have added a isset for the button to query to run on submitting as well. — Bing, Oct 03 '17 at 06:59
Numeric values in SQL statements don't need to be in quotes - other datatypes do. BUT you should be using prepared statements and bind variables. — Nigel Ren, Oct 03 '17 at 07:00

score 0 · Answer 1 · answered Oct 03 '17 at 06:53

0

Change your syntax from

$sql = "INSERT INTO tested (itemno, item, quantity) VALUES ('1', $item, $quantity)";

to

$sql = "INSERT INTO tested (itemno, item, quantity) VALUES ('1', '$item', '$quantity')";

answered Oct 03 '17 at 06:53

Rajeev Radhakrishnan

954
9
17

1

No. You may solved his problem, but the code is still really vulnerable to SQL injection. You would better recommend him to read up on how to prevent them, instead of providing him more bad code. – Twinfriends Oct 03 '17 at 06:55
Might want to tell OP never to use this code in production – Rotimi Oct 03 '17 at 06:58

TarangP · Answer 2 · 2017-10-03T07:01:09.413

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to Sql Injection.

$someVariable = $_POST['user_input'];     
mysql_query("INSERT INTO `table` (`column`) VALUES ('$someVariable')");

user can input something like value'); DROP TABLE table;--, and the query becomes:

INSERT INTO `table` (`column`) VALUES('value'); DROP TABLE table;--')

So this is not Secure Way.

and in this example your mistake is you miss single quote in variable that you pass in values of query.

Your Query

  $sql = "INSERT INTO tested (itemno, item, quantity) VALUES ('1', $item, $quantity)";

New Query

$sql = "INSERT INTO tested (itemno, item, quantity) VALUES ('1', '$item', '$quantity')";

double quotes are for delimited identifiers.and single quotes are for literals.

for more you can see this stack overflow answer

Rishabh Gogia · Answer 3 · 2017-10-03T07:35:40.380

You should put values under single quotes. This will work for you but this will not prevent your code from sql injection:

$sql = "INSERT INTO tested (itemno, item, quantity) VALUES ('1', '$item', '$quantity')";

So the above method is correct but not secure.

When creating a prepared statement, you create a query in which you add placeholders instead of the raw values:

$stmt = $conn->prepare("INSERT INTO Users (email, pw) VALUES (?, ?)");

The question marks are the placeholders and are later replaced by using the bind_param method:

$stmt->bind_param('ss', $email, $pw);

The ss part of the bind call tells the mysql db that its two strings that are passed to the database.

(s for string, i for int etc).

score 0 · Answer 4 · answered Oct 03 '17 at 07:17

Use prepared statements. It'll prevent sql injection

$sql = "INSERT INTO tested (itemno, item, quantity) VALUES (?,?,?)";//3placeholders for three variables 

$statement = $conn->prepare($sql); 
$statement->bind_param('iss',1,$item, $quantity);//the letter i denotes an integer, while s is a string 
if(statement->execute() === true) {//did it save successfully?
//everything went fine
echo 'saved'; 
} else {
echo $conn->error; 
}

query is not getting updated

4 Answers4