B4J Question Update a record in mysql using php

[saeed10051]

Hi,
i am trying to update a record in an online mysql database. using following php script

<?php
  $conn = new mysqli("pdb48.awardspace.net", "3373050_restaurant", "abc123", "3373050_restaurant");
 
  if ($conn->connect_error) {
    die("ERROR: Unable to connect: " . $conn->connect_error);
  }

  echo "Connected to the database.<br>";

  $id = $_REQUEST['id'];
$name = $_REQUEST['name'];
$price = $_REQUEST['price'];
    
 
  $sql = "UPDATE menuitems SET itemname=$name WHERE itemid=$id";

if ($conn->query($sql) === TRUE) {
  echo "Record Updated successfully";
} else {
  echo "Error updating record: " . $conn->error;
}


$conn->close();
?>

i am getting following error in the B4j log
Connected to the database.<br>Error updating record: Unknown column 'abc' in 'field list'

here i am writing abc in the itemname field so that the record value for itemname changes from existing value to abc but it seems that php is considering this abc value as a new column in the table.

 

[OliverA]

1) the way you build the $sql variable, the data you are passing is not quoted. Therefore SQL interprets it as a column name. Therefore your error message
2) You need to quote data you pass in an SQL statement. The problem is that by building your SQL statements that way, you are opening yourself up to SQL injection attacks
3) Use parameterized queries (aka prepared statements) to pass SQL statements and there corresponding values to your SQL server. It’s more work, but it’s worth it from a security perspective. See https://websitebeaver.com/prepared-statements-in-php-mysqli-to-prevent-sql-injection

 

[mangojack]

I'm certainly not a expert with PHP but something that caught my eye , the PHP file contains DB username & password ?

At the very least ,this should be in a separate file outside of the web root folder and read / included in your php script.

I use something like ..

<?php
include ("/home/xxxabc/xxxdef/config.php");

$dbname = "abc123_testdb";
$conn = new mysqli($host,$user,$pw,$dbname);

There is probably much more secure methods recommended .. but this is currently sufficient for me / my insensitive data.

Feel free to correct me / advise otherwise.

 

[saeed10051]

1) the way you build the $sql variable, the data you are passing is not quoted. Therefore SQL interprets it as a column name. Therefore your error message
2) You need to quote data you pass in an SQL statement. The problem is that by building your SQL statements that way, you are opening yourself up to SQL injection attacks
3) Use parameterized queries (aka prepared statements) to pass SQL statements and there corresponding values to your SQL server. It’s more work, but it’s worth it from a security perspective. See https://websitebeaver.com/prepared-statements-in-php-mysqli-to-prevent-sql-injection

thanks OliverA. But regarding point 1 above i am using following code for delete and it is working fine. here also i am using same syntax for building $sql variable. can you please tell me why this one is working while update one is not working

<?php
  $conn = new mysqli("pdb48.awardspace.net", "3373050_restaurant", "abc123", "3373050_restaurant");
 
  if ($conn->connect_error) {
    die("ERROR: Unable to connect: " . $conn->connect_error);
  }

  echo "Connected to the database.<br>";

  $id = $_REQUEST['id'];
 
  $sql = "DELETE FROM menuitems WHERE itemid=$id";

if ($conn->query($sql) === TRUE) {
  echo "Record deleted successfully";
} else {
  echo "Error deleting record: " . $conn->error;
}


$conn->close();
?>

 

[OliverA]

Because if $id is numeric, than that is interpreted as a value, since no table and column names are numeric.

 

[saeed10051]

1) the way you build the $sql variable, the data you are passing is not quoted. Therefore SQL interprets it as a column name. Therefore your error message
2) You need to quote data you pass in an SQL statement. The problem is that by building your SQL statements that way, you are opening yourself up to SQL injection attacks
3) Use parameterized queries (aka prepared statements) to pass SQL statements and there corresponding values to your SQL server. It’s more work, but it’s worth it from a security perspective. See https://websitebeaver.com/prepared-statements-in-php-mysqli-to-prevent-sql-injection

Thanks Boss.
It is working now. I am using prepared statements now.