Update field value in mysql table using php programming

Question

$id = $_REQUEST["Uid"];

$query = "update prd set name='".$_POST['nm']."', char='".$_POST['ch']."', price='".$_POST['pr']."', sp_pri='".$_POST['spr']."', is_eli='".$_POST['enb']."', upd='".$_POST['ud']."', img='".$_FILES['img']['name']."', c_id='".$_POST['cid']."' where id=".$id;

$r = mysql_query($query) or die(mysql_error());
if ($r == 1)
    echo "Record Updated";

I'm getting the following error from this query but I'm not sure how to solve it - the SQL looks OK to me! What am I doing wrong?

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 'char='b', price='150', sp_pri='100', is_eli='Yes', upd='Mon Jul 23 2012 17:23:24' at line 1

Say hello to SQL Injections. Tip : prefer use prepared statements, don't use mysql_* functions, and don't use $_POST variables in the request directly. — BMN, Jul 25 '12 at 08:25
One more reason not to use the mysql extension is it's being deprecated (as is alluded to on most of the [mysql manual pages](http://php.net/mysql_connect)). PDO and mysqli are the recommended extensions to use. Also, `or die(mysql_error())` should never appear in production code, as [`die`](http://www.phpfreaks.com/blog/or-die-must-die) breaks HTML output and database error messages should never be revealed to non-admin users as it [discloses too much information](http://msdn.microsoft.com/en-us/library/ms995351.aspx#securityerrormessages_topic2). — outis, Jul 25 '12 at 08:31
possible duplicate of [PHP/MySQL Update code](http://stackoverflow.com/q/817653/), [Yii Framework/PDO getting error CDbCommand failed to execute the SQL statement: SQLSTATE\[42000\]](http://stackoverflow.com/q/2316616/), [Why does this SQL INSERT statement return a syntax error?](http://stackoverflow.com/q/912466/), [Syntax Error with MySQL Query](http://stackoverflow.com/q/1128613/) — outis, Jul 25 '12 at 08:45

score 8 · Answer 1 · answered Jul 25 '12 at 08:27

char is a reserved word within SQL. If you use such keywords as column names you must enclose them in backticks (as you should always do with column names)!

$query="update prd set `name`='".$_POST['nm']."', `char`='".$_POST['ch']."', `price`='".$_POST['pr']."', `sp_pri`='".$_POST['spr']."', `is_eli`='".$_POST['enb']."', `upd`='".$_POST['ud']."', `img`='".$_FILES['img']['name']."', `c_id`='".$_POST['cid']."' where `id`=".$id;

Furthermore by adding $_POST variables directly into a query without sanitizing them before, you open your system to any kind of attacks, that compromise your data and web site.

Have a look at mysqli and PDO to circumvent such problems.

score 1 · Answer 2 · answered Jul 25 '12 at 08:32

1

In addition to Sirko's suggestions, in the assignment to field upd which is a date - you should use STR_TO_DATE

answered Jul 25 '12 at 08:32

Nir Alfasi

53,191
11
86
129

Update field value in mysql table using php programming

2 Answers2