Using .php to write data to database

Question

Can someone point the fault in this code? I'm unable to update data to the database. We are sending a text message to the server, and this file here decodes and sets it in the database. But this case over here is not working for some reason. I checked and tried to troubleshoot, but couldn't find a problem.

case 23:
  // Gather Variables
  $Message = preg_replace("/\s+/","%20", $Message);

  $UnixTime = time();
  $cycle = explode(":", $Message);
  $machine_press = $cycle[0];
  $machine_pct_full = $machine_press/20;
  $machine_cycles_return = $cycle[1];
  $machine_cycles_total = $cycle[2];

 // Build SQL Statement to update static values in the machine table
 $sql = "UPDATE `machines` SET `machine_last_run`=".$UnixTime.",`machine_press`=".$machine_press.",`machine_pct_full`=".$machine_pct_full.",`machine_cycles_return`=".$machine_cycles_return.",`machine_cycles_total`=".$machine_cycles_total." WHERE `machine_serial`='$MachSerial'";

 // Performs the $sql query on the server to update the values
 if ($conn->query($sql) === TRUE) {
   // echo 'Entry saved successfully<br>';
 } else {
    echo 'Error: '. $conn->error;
 }


  $sql = "INSERT INTO `cycles` (`cycle_sequence`,`cycle_timestamp`,`cycle_did`,`cycle_serial`,`cycle_03_INT`,`cycle_14_INT`,`cycle_15_INT`,`cycle_18_INT`)";
  $sql = $sql . "VALUES ($SeqNum,$UnixTime,'$siteDID','$MachSerial',$machine_press,$machine_cycles_total,$machine_cycles_return,$machine_pct_full)";

  // Performs the $sql query on the server to insert the values
  if ($conn->query($sql) === TRUE) {
    // echo 'Entry saved successfully<br>';
  } else {
    echo 'Error: '. $conn->error;
  }

break;

@j08691 I dont get any errors.. We are using the codeigniter framework so there is no way i can fetch for errors.. or may be i dont know the way to look up any errors. — Ammar Surti, Jul 14 '17 at 21:06
Look... quite simply your question doesn't include any information. What people need here is, at the very least, example input that should work. So all those variables you have at the top of the case statement, you should be var_dump(..); exit(); And see what you have there. You can't even assure anyone at present that the code you've snipped even gets run right now. — gview, Jul 14 '17 at 21:11
What is the actual resulting SQL query being executed? What does `$conn->query($sql)` return? — David, Jul 14 '17 at 21:12
[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)***. Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Jul 14 '17 at 21:12
I think you are just missing a space in-between the $sql statement concatenation. `$sql = $sql." VALUES` instead of `$sql = $sql."VALUES` — Yolo, Jul 14 '17 at 21:30
@yolo I checked, that isnt the problem here. I am not what more information should i provide to resolve this issue? — Ammar Surti, Jul 18 '17 at 11:51
@gview I understand what you mean. There is too much going on here, thats why i thought this information would help. But to summarize, I am sending a text message to our server (SMS) from my phone. On the server side i have an sms software that sends all received sms to the above file (above just shows part of it). every message has an ID in the beginning. so in this case, the ID is 23 as the case number is 23. and after receiving that message, it break it up and store the information to our database with the sql query. all other cases work, and i am trying to fix this one. — Ammar Surti, Jul 18 '17 at 11:58

score 0 · Answer 1 · answered Jul 14 '17 at 21:25

0

More information is required to help you out with your issue.

First, to display errors, edit the index.php file in your Codeigniter project, update where it says

define('ENVIRONMENT', 'production');

to

define('ENVIRONMENT', 'development');

Then you'll see exactly what the problem is. That way you can provide the information needed to help you.

answered Jul 14 '17 at 21:25

Oluwaseye

685
8
20

Where exactly would i see this error? and will this in any way affect our online website which depends on this index.php file? – Ammar Surti Jul 18 '17 at 11:46
This allows you see the error on the website. If you have a separate DEV environment, you can update this there, fix the errors and push the updates to your LIVE site – Oluwaseye Jul 18 '17 at 15:07
Try sharing your controller and model with the community, you can paste and share them via http://codepad.org/ or any other php sandox. Do you want me to share what your code should look like in Codeigniter ? – Oluwaseye Jul 18 '17 at 15:17
I found the problem. The problem isnt with the code, but with the text message it receives. right now the code is not ignoring the blank spaces in the incoming message. what should i add in my php code in order to ignore blank spaces in the messages and merge everything together before splitting it using ":" (as mentioned in the code) – Ammar Surti Jul 18 '17 at 19:10

Yolo · Answer 2 · 2017-07-18T19:10:36.697

I just saw that you are inserting strings when not wrapping them in apostrophe '. So you queries should be:

$sql = "UPDATE `machines` SET `machine_last_run`='".$UnixTime."',`machine_press`='".$machine_press."',`machine_pct_full`='".$machine_pct_full."',`machine_cycles_return`='".$machine_cycles_return."',`machine_cycles_total`='".$machine_cycles_total."' WHERE `machine_serial`='$MachSerial'";

and

$sql = "INSERT INTO `cycles` (`cycle_sequence`,`cycle_timestamp`,`cycle_did`,`cycle_serial`,`cycle_03_INT`,`cycle_14_INT`,`cycle_15_INT`,`cycle_18_INT`)";
$sql = $sql . " VALUES ('$SeqNum','$UnixTime','$siteDID','$MachSerial','$machine_press','$machine_cycles_total','$machine_cycles_return','$machine_pct_full')";

For any type of unknown problems I can recommend turning on PHP and SQL errors and use a tool called postman that i use to test my apis. You can mimic requests with any method, headers and parameters and send an "sms" just like your provider or whatever does to your API. You can then see the errors your application throws.

EDIT

I tested your script using a fixed version with ' and db.

$Message = "value1:value2:value3";
$MachSerial = "someSerial";
$SeqNum = "someSeqNo";
$siteDID = "someDID";

$pdo = new PDO('mysql:host=someHost;dbname=someDb', 'someUser', 'somePass');

// Gather Variables
$Message = preg_replace("/\s+/","%20", $Message);

$UnixTime = time();
$cycle = explode(":", $Message);
$machine_press = $cycle[0];
$machine_pct_full = (int)$machine_press/20; // <----- Note the casting to int. Else a warning is thrown.
$machine_cycles_return = $cycle[1];
$machine_cycles_total = $cycle[2];

// Build SQL Statement to update static values in the machine table
$sql = "UPDATE `machines` SET `machine_last_run`='$UnixTime',`machine_press`='$machine_press',`machine_pct_full`='$machine_pct_full',`machine_cycles_return`='$machine_cycles_return',`machine_cycles_total`='$machine_cycles_total' WHERE `machine_serial`='$MachSerial'";

try {
    $pdo->query($sql);
} catch (PDOException $e) {
    echo 'Query failed: ' . $e->getMessage();
}

$sql = "INSERT INTO `cycles` (`cycle_sequence`,`cycle_timestamp`,`cycle_did`,`cycle_serial`,`cycle_03_INT`,`cycle_14_INT`,`cycle_15_INT`,`cycle_18_INT`)";
$sql = $sql . "VALUES ('$SeqNum','$UnixTime','$siteDID','$MachSerial','$machine_press','$machine_cycles_total','$machine_cycles_return','$machine_pct_full')";

try {
    $pdo->query($sql);
} catch (PDOException $e) {
    echo 'Query failed: ' . $e->getMessage();
}

It totally works. Got every cycle inserted and machines updated. Before i fixed it by adding wrapping ' i got plenty of errors.

THank you for the reply. But seems like this isnt the issue with the code. — Ammar Surti, Jul 18 '17 at 18:38
I found the problem. The problem isnt with the code, but with the text message it receives. right now the code is not ignoring the blank spaces in the incoming message. what should i add in my php code in order to ignore blank spaces in the messages and merge everything together before splitting it using ":" (as mentioned in the code) — Ammar Surti, Jul 18 '17 at 19:10
You are already removing any whitespace in your text but replacing it with the encoded version of a space. If you want to remove them all just use `$str=preg_replace('/\s+/', '', $str);`. Maybe have a look at the code in my edited answer again. — Yolo, Jul 18 '17 at 19:16

score 0 · Answer 3 · answered Jul 18 '17 at 19:19

Alright so this is the solution:

i replaced the line:

$Message = preg_replace("/\s+/","%20", $Message);

with:

$Message = preg_replace("/\s+/","", $Message);

This removes all blank spaces in my text message and makes it a single string before breaking and assigning it to different tables in the database. I understand this wasnt really a problem with the script and no one around would have known the actual problem before answering. and thats why i am posting the solution just to update the team involved here.

Using .php to write data to database

3 Answers3