B4J Question Update a record in mysql using php

saeed10051

Active Member
Licensed User
Longtime User
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

Expert
Licensed User
Longtime User
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
 
Last edited by a moderator:
Upvote 1

mangojack

Well-Known Member
Licensed User
Longtime User
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.
 
Upvote 0

saeed10051

Active Member
Licensed User
Longtime User
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();
?>
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
Because if $id is numeric, than that is interpreted as a value, since no table and column names are numeric.
 
Upvote 0

saeed10051

Active Member
Licensed User
Longtime User
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.
 
Upvote 0
Top