Update where id=$id

Question

I have this query to submit data into the database:

$sql = "UPDATE table SET user='$user', name='$name' where id ='$id'";

the id will be obtained via url EX localhost/index.php?id=123

$id=$_GET['id']

The query will not work correctly; the data will not update. If I write :

$sql = "UPDATE table SET user='$user', name='$name' where id ='123'";

It works fine.

If I echo the ID it will show the correct result, 123.

Where is the problem?

This code screams SQL INJECTION!!! You should probably read up on what they are and how to prevent them. — Gazler, Nov 29 '11 at 15:22
Have a read of this question/answer - it explains the methods for avoiding SQL injection : http://stackoverflow.com/questions/60174/best-way-to-stop-sql-injection-in-php — Manse, Nov 29 '11 at 15:24
I'm quite sure your tablename and some columnnames are keywords, put them in inverted single qoutes: `UPDATE \`table\` SET \`user\`='$user', \`name\`='$name' WHERE \`id\` = $id` — user254875486, Nov 29 '11 at 15:24

score 12 · Answer 1 · answered Nov 29 '11 at 16:15

run ALL your queries the way you can get the error message along with erroneous query.
so, at least this way

$sql = "UPDATE table SET user='$user', name='$name' where id ='$id'";
$res = mysql_query($sql) or trigger_error(mysql_error()." in ".$sql);

and it will tell you where is the problem.

It is WAY more convenient, precise and faster than asking questions here.

score 5 · Answer 2 · answered Nov 29 '11 at 15:23

I'm guessing your problem is mal-formed SQL due to unescaped data interpolation - an SQL injection hole.

What does your actual generated query look like? Not the code that creates the sql (which you've got above), but the actual SQL after the variables are inserted?

I'm guessing it'll look something like this:

UPDATE table SET user='fred', name='O'Brien' where id='123';
                                     ^--unescaped quote

causing a syntax error.

If you're running the query like this:

$result = mysql_query($sql);

then change it to be

$result = mysql_query($sql) or die(mysql_error());

so you'll immediately get feedback if the query fails for any reason.

And then read up about SQL injection holes

the result show this: UPDATE table SET user='robert', name='bob' where id=" — Geme, Nov 29 '11 at 16:19

score 1 · Answer 3 · answered Jul 23 '20 at 00:10

1

$id = $_GET['id']

<form action="#.php" method="POST">
      <input type="hidden" name="id" value="<?php echo $id?>">
</form>

then, inside PHP block,

$id = $_POST['id'];
$sql = "UPDATE table SET user='$user', name='$name' where id ='$id'"

answered Jul 23 '20 at 00:10

Izzat Najmi

11
1

score 0 · Answer 4 · answered Nov 29 '11 at 15:22

0

Without getting into the issue of how bad it is to pull data right from the GET array, I'd start by suggesting you properly escape your variables. I assume ID is an integer, so there's no need for singlequotes around it.

$sql = "UPDATE table SET user='".$user."', name='".$name."' where id=".$id;

See if that works.

answered Nov 29 '11 at 15:22

nageeb

2,002
1
13
25

1

This will change absolutely NOTHING. `$x = "x $a x"` is treated exactly the same as `$x = 'x ' . $a . ' x';` by PHP. All you've done is rearrange the deck chairs on a sinking ship. – Marc B Nov 29 '11 at 15:24
1

@MarcB He is talking about the single quotes being passed to the database engine. – HaLeiVi Sep 14 '16 at 05:43
@HaLeiVi: irrelevant. the `'` are inside a `"`-quoted string, so they're not quotes, they're just some random character as far as PHP is concerned. – Marc B Sep 14 '16 at 14:09
@MarcB The error is not a php error. It is a database error. This $sql is being passed to the database which won't read the (") but will read the ('). And as nageeb rightly advised, a value surrounded by quotes is a string but the id field is expecting a number. – HaLeiVi Sep 14 '16 at 14:17
it's mysql. it autoconverts numbers as appropriate. `1 = '1'` is true in mysql (assuming strict mode is off) – Marc B Sep 14 '16 at 14:18

score 0 · Answer 5 · answered Nov 29 '11 at 15:23

0

TableName should be there ....you have not used table name in your query..Echo the $sql and then try executing in phpmyadmin.

answered Nov 29 '11 at 15:23

vikas

737
1
7
12

score 0 · Answer 6 · answered Nov 29 '11 at 15:23

First of all, you're wide open to SQL Injection attacks if you do it like this. Anyone can just alter the part after id= to anything they like and modify your database with that.

Secondly, I see you pass an id to the script, but where does it determine the $user and $name values? Seems like your code posted is incomplete.

Update where id=$id

6 Answers6