B4J Question Update a record in mysql using php

saeed10051 · May 19, 2020

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

B4X:

<?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 · May 20, 2020

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 · May 20, 2020

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 ..

B4X:

<?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 · May 20, 2020

OliverA said:
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

B4X:

<?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 · May 20, 2020

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

saeed10051 · May 21, 2020

OliverA said:
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.

B4J Question Update a record in mysql using php

saeed10051

Active Member

OliverA

Expert

mangojack

Expert

saeed10051

Active Member

OliverA

Expert

saeed10051

Active Member

Similar Threads

B4J Question Update a record in mysql using php

saeed10051

Active Member

OliverA

Expert

mangojack

Expert

saeed10051

Active Member

OliverA

Expert

saeed10051

Active Member

Similar Threads

Privacy & Transparency

Privacy & Transparency