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
– BMN, Commented Jul 25, 2012 at 8: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). PDO and mysqli are the recommended extensions to use. Also, or die(mysql_error()) should never appear in production code, as die breaks HTML output and database error messages should never be revealed to non-admin users as it discloses too much information. — outis
– outis, Commented Jul 25, 2012 at 8:31
possible duplicate of PHP/MySQL Update code, Yii Framework/PDO getting error CDbCommand failed to execute the SQL statement: SQLSTATE[42000], Why does this SQL INSERT statement return a syntax error?, Syntax Error with MySQL Query — outis
– outis, Commented Jul 25, 2012 at 8:45

Sirko · Accepted Answer · 2012-07-25 08:27:01Z

8

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.

answered Jul 25, 2012 at 8:27

Sirko

74.3k19 gold badges157 silver badges194 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nir Alfasi · Accepted Answer · 2012-07-25 08:32:13Z

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, 2012 at 8:32

Nir Alfasi

53.6k11 gold badges94 silver badges138 bronze badges

Collectives™ on Stack Overflow

Update field value in mysql table using php programming

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related