Manually updating the database but not via php

Question

Can someone point why the below code is not working?

$insert="insert into $table (id,date,updatedtime,09to10,09to10comments)".
        " values ($id,curdate(),curtime(),'test','test1')";
print "query is<br> $insert<br>";
$escaped_insert=mysqli_real_escape_string($connect,$insert);
$check_query=mysqli_query($connect,$escaped_insert);

if(!$check_query)
{
    print "unable to update the database";
}
else
{
print "successfully updated the database";
}

it is showing the below error output in webpage

query is
insert into tasks (id,date,updatedtime,09to10,09to10comments) values (408112,curdate(),curtime(),'test','test1')
unable to update the database

but manually it works when i execute same query in mysql.

mysql> insert into tasks (id,date,updatedtime,09to10,09to10comments) values (408
112,curdate(),curtime(),'test','test1');
Query OK, 1 row affected, 46 warnings (0.05 sec)

Change your code to: `if(!$check_query) { die(mysqli_error($connect)); }` — Amal Murali, Mar 15 '14 at 15:23
@Amal Murali - this is the output. `query is insert into tasks (id,date,updatedtime,09to10,09to10comments) values (408112,curdate(),curtime(),'test','test1') 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 '\'test\',\'test1\')' at line 1` — divakar.scm, Mar 15 '14 at 15:35
even tested the query by removing single quotes. the output is below. `query is insert into tasks (id,date,updatedtime,09to10,09to10comments) values (408112,curdate(),curtime(),test,test1) Unknown column 'test' in 'field list'` — divakar.scm, Mar 15 '14 at 15:41
Please read this: http://stackoverflow.com/a/12720360/476 and [The Great Escapism (Or: What You Need To Know To Work With Text Within Text)](http://kunststube.net/escapism/). You're applying escaping entirely wrong. — deceze, Mar 15 '14 at 15:49

Amal Murali · Answer 1 · 2014-03-15T16:25:22.210

Why doesn't it work?

You're using mysqli_real_escape_string() incorrectly. It escapes a single string, not the whole query. If you've got multiple user input variables, you need to escape them individually. Escaping the whole SQL query will not work.

The goal of escaping is to prevent characters with special meaning from having that special meaning. Since you are escaping the whole SQL query containing all your quoted values, you are also escaping the quotes and stopping them from correctly quoting the values.

This is how you should use it:

// Escape ALL the input prior to executing the query
$id = mysqli_real_escape_string($connect, $id);

// The query
$insert = "INSERT INTO $table (ID,`date`,UPDATEDTIME,09TO10,09TO10COMMENTS)
    VALUES ($id,CURDATE(),CURTIME(),'test','test1')";

// Execute the query
$check_query = mysqli_query($connect, $insert);

Better way: Prepared Statements

You can manually escape all the user input yourselves. That'd work, sure. But with prepared statements, you don't have to worry about escaping at all. The bound variables are sent separately, so there's no chance of SQL injection (provided you use it correctly). It won't forget to escape the user input, or miss out on any special characters which could be used to inject some malicious SQL.

Here's how you'd do it with prepared statements:

/* Create a new mysqli object with database connection params */
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

// The query
$insert = "INSERT INTO $table (ID,`date`,UPDATEDTIME,09TO10,09TO10COMMENTS)
        VALUES (?,CURDATE(),CURTIME(),'test','test1')";

if ($stmt = $mysqli->prepare($insert)) {

    /* Bind parameters: s - string, i - int, etc */
    $stmt->bind_param('i', $id);

    /* Execute it */
    if ($stmt->execute()) {
        // Execution successful
        // Do whatever you want
    }

    /* Close statement */
    $stmt -> close();

}

Manually updating the database but not via php

1 Answers1

Why doesn't it work?

Better way: Prepared Statements