0

I'm inserting some values to a table using a sql statement. All the values get inserted, except the 'qty' column. When I do an echo for sql statement, it shows the value has been assigned to the sql statement. Table column data type is INT. Can anyone help me to spot the problem?

$it_code = $_POST['id']; 

//Prompt user to enter required qty
echo '<script>'; 
echo 'var qty = prompt("Enter Qty", "");';
echo '</script>';

//Get the item details from item table
$sqlString = "Select * from items where it_code='".$it_code."'";
$objItem = new Allfunctions;
$result = $objItem->retriveData($sqlString);

//Get the selected qty from java script to php variable
$qty = "<script>document.writeln(qty);</script>";
$user_id =$_SESSION['user_id'];  

//Insert to orders table
$sqlStringInsert = "INSERT INTO orders(user_id,it_code,qty,status,tr_timestamp) VALUES ('$user_id','$it_code','$qty','PENDING',CURRENT_TIMESTAMP())";
echo $sqlStringInsert;
$objItem->addToTable($sqlStringInsert,'Succsessfully added.','Error adding'); // This is a custom built method to insert data in to a table by taking the sql statement as the parameter

Following is the sql statement generated, enter image description here

Following is the table data. Qty is always 0 eventhood the sql statement had a value at qty column.

enter image description here

enter image description here

enter image description here

lakshitha dilhan
  • 83
  • 9
  • There is no way that's the SQL statement that's getting echoed by that script. Currently you show $qty as being assigned to a string of HTML right before being injected into $sqlStringInsert. Are you sure you're showing the actual output of the script here? – Josiah Hudson Feb 01 '23 at 16:10
  • Can you please post the sql statement as text instead of a picture. Then we could use it and work with it in order to try to help you – Peter206 Feb 01 '23 at 16:11
  • INSERT INTO orders(user_id,it_code,qty,status,tr_timestamp) VALUES ('1','s0015','10 ','PENDING',CURRENT_TIMESTAMP()) – lakshitha dilhan Feb 01 '23 at 16:12
  • Above is the value echoed by php for the variable '$sqlStringInsert' – lakshitha dilhan Feb 01 '23 at 16:14
  • Strange, the resulting query looks correct. Did you check: a.) when executing the query are there any errors? b.) are there any triggers on the table? A trigger could theoretically change the value to 0. – Peter206 Feb 01 '23 at 16:17
  • There are no triggers in the table. All other values get inserted except qty and I don't get any errors either. I have a suspicion that this is because I'm getting the value from java script. Could it be the reason? – lakshitha dilhan Feb 01 '23 at 16:19
  • if you suspect that javascript has an influence please add the tag javascript to the question. You could try to use [mysql query profiler](https://web.archive.org/web/20110609054749/http://dev.mysql.com/tech-resources/articles/using-new-query-profiler.html) – Peter206 Feb 01 '23 at 16:21
  • 1
    You can't embed javascript inside PHP! – Grasper Feb 01 '23 at 16:21

1 Answers1

4

Obligatory warning

The code as you have shown it should not be used in production as it is vulnerable to SQL-injection attacks. Preventing this is well covered on this site in other answers.

Answer

The issue is that the value of $qty you are injecting into your SQL is the string "<script>document.writeln(qty);</script>". You just can't see it because you are likely echoing it out to the browser to test it.

Wrap your echoing of the SQL statement in a call to htmlentities() (docs) to see what's actually happening.

Depending on the version and settings, MySQL is very forgiving of bad data being injected to a column. In this case, it sees a string of text being inserted to a numeric column, and just truncates it to 0. See this in action here:

CREATE TABLE Foo (
  Id INT,
  Qty INT
 );
 
INSERT INTO Foo(Id, Qty) VALUES (1, 'a string of text');

SELECT * FROM Foo; -- returns a row (1, 0)

https://www.db-fiddle.com/f/dEoaYGEyXEjs6ocVBwyyyr/1

Josiah Hudson
  • 1,015
  • 8
  • 14