Error in an update mysql query execution with php and mysqli

Question

I am trying to do a small project. My task to create an update form with HTML and PHP. But I am getting this error given below:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's standard dummy text ever since the 1500s, when an unknown printer.' , exp_time' at line 1

I am using Laragon for php and HeidiSQL 9.5 for mysql server.

My database connection is okay. I can fetch data from the database using the SELECT query in the same file. I think something is wrong in my code. So please help me the code is given below:

    <?php
    require('auth.php');
    require('db.php');
    $id=$_REQUEST['id'];
    $query = "SELECT * FROM experience where expid='".$id."'";
    $result = mysqli_query($con,$query) or die ( mysqli_error($con));
    $row = mysqli_fetch_assoc($result);

    $status = "";
    if(isset($_POST['new']) && $_POST['new']==1)
    {


    $exp_title = $_REQUEST['exp_title'];
    $exp_description = $_REQUEST['exp_description'];
    $exp_time = $_REQUEST['exp_time'];
    $update="UPDATE experience SET exp_title='".$exp_title."' , exp_description='".$exp_description."' , exp_time='".$exp_time."'
    WHERE expid='".$id."'";
    mysqli_query($con, $update) or die ( mysqli_error($con));
    $status = "Record Updated Successfully. </br></br>
    <a href='dashboard.php'>View Updated Record</a>";
    echo '<p style="color:#FF0000;">'.$status.'</p>';
    }else {
    ?>

Your variable values have quotes in them, so you're ending up with SQL like `UPDATE table SET column='my value's' ...` which isn't valid. Use prepared statements with parameters. — Jonnix, Apr 03 '19 at 13:02
Your script is wide open to [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either the `MYSQLI_` or `PDO` API's — RiggsFolly, Apr 03 '19 at 13:03
Also please stop using `$_REQUEST`. Use the relevant superglobals for the request being made. — Jonnix, Apr 03 '19 at 13:12

Darshan Mehta · Accepted Answer · 2019-04-03T13:19:47.757

-3

You need to escape the single quotes using php's str_replace, e.g.:

$exp_title = str_replace("'", "\'", $_REQUEST['exp_title']);
$exp_description = str_replace("'", "\'", $_REQUEST['exp_description']);
$exp_time = $_REQUEST['exp_time'];
$update="UPDATE experience SET exp_title='".$exp_title."' , exp_description='".$exp_description."' , exp_time='".$exp_time."'
WHERE expid='".$id."'";

However, you should really really use preparedstatements instead of concatenating strings and escaping characters, e.g.:

$exp_title = $_REQUEST['exp_title'];
$exp_description = $_REQUEST['exp_description'];
$exp_time = $_REQUEST['exp_time'];
$stmt = $conn->prepare("UPDATE experience SET exp_title= ?, exp_description = ?, exp_time = ? WHERE expid = ?");
$stmt->bind_param("types", $exp_title, $exp_description, $exp_time, $id);

edited Apr 03 '19 at 13:19

answered Apr 03 '19 at 13:05

Darshan Mehta

30,102
11
68
102

Thank you. This worked for me. I will mark it as the answer. I can't use PDO or Prepared statement because my teacher won't accept it. I don't know why. BUT THANKS A LOT, sir. You are the man. – Mohsin Apr 03 '19 at 13:08
1

I mean, you're not even wrong, but this really isn't the kind of solution I'd recommend to anyone, perhaps even especially new developers. But maybe that's just me. – Jonnix Apr 03 '19 at 13:09
Nothing to do. Please drop the teacher matter now. I got my answer. My homework is done. @Darshan Metha thanks a lot. – Mohsin Apr 03 '19 at 13:11
1

@all I have added an update to the answer and suggested the recommended approach. – Darshan Mehta Apr 03 '19 at 13:17

Error in an update mysql query execution with php and mysqli

1 Answers1