mysql update not working when using variable in condition

Question

When I use a variable in my WHERE clause of Update the updation doesnt take place.

            $eventid=$_GET['id'];        
            $sql = "UPDATE events SET name=:name WHERE id=:id";
            $q = $conn->prepare($sql);
            $q->execute(array(':name'=>$name,':id'=>$eventid));

When I echo $_GET['id'] I get the correct value. $_GET['id'] is the value I have passed from another page

You are vulnerable to [sql injection attacks](http://bobby-tables.com). Enjoy having your server pwn3d... — Marc B, Sep 02 '16 at 18:33
Use prepared statements which are supported by both [MySQLi](http://php.net/manual/en/mysqli.prepare.php) and [PDO](http://php.net/manual/en/pdo.prepared-statements.php). — , Sep 02 '16 at 18:36
check [**here**](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Juan Carlos Oropeza, Sep 02 '16 at 18:36

score 1 · Answer 1 · answered Sep 02 '16 at 18:37

1

People are saying you're vulnerable to sql injection attacks because you're passing an id via a $_GET parameter to an update statement where you directly use the $_POST superglobal.

In order to make it more secure, you could start by passing the ID via $_POST as well, and not using $_POST directly in your SQL.

But that's not the question you asked.

I would hazard to guess it's not updating because you're passing ID as a string, which it probably isn't.

Try changing

WHERE id='".$_GET['id']."'");\

to

WHERE id=".$_GET['id']."");

answered Sep 02 '16 at 18:37

Lynne

150
12

The updation is still not working Thanks for the help – Nazim Mohamed Sep 02 '16 at 18:46
I was passing the $_GET to another variable and used it in UPDATE.Just for the sake of the question I kept it there.Would it solve the SQL Injection issues? – Nazim Mohamed Sep 02 '16 at 18:50
@NazimMohamed That wont solve sql injection. She is saying maybe you are trying to compare a string like a number. – Juan Carlos Oropeza Sep 02 '16 at 18:51
Your SQL injection issues go far deeper than that, there's more to it than just fixing a single variable. It would likely require a fairly heavy rework of whatever system you are using. I would recommend reading the link provided in the comments on your OP and go from there. It's really an entirely different question. – Lynne Sep 02 '16 at 18:55
@Lynne I have used real_escape_string.Wont it help in reducing SQL injetion? – Nazim Mohamed Sep 02 '16 at 19:09
That will help prevent against corrupted data. I feel like there's a lot more to be considered when it comes to security, though. – Lynne Sep 02 '16 at 19:23
@NazimMohamed SQL injection happen inside the query it self , so lets say that you tried to execute this query : ` SELECT name FROM events;` all what the hackers can do this if they could access to your Query : ` SELECT name FROM events ; DROP TABLE events ` , Here what happen is a disaster cause if that table was important then say bye bye to the table and the database , so the best way or a good way to avoid that is by not passing direct values and variables to the query , Read this for examples and info's : [link](http://php.net/manual/en/pdo.prepared-statements.php) . – Laith Sep 02 '16 at 21:44

score 1 · Answer 2 · answered Sep 02 '16 at 18:50

1

Save your query to a string variable so you can debug what are you sending to db MySQL. Then try to run the result query direct on the db.

$DBcon->query(@strSQL);

answered Sep 02 '16 at 18:50

Juan Carlos Oropeza

47,252
12
78
118

mysql update not working when using variable in condition

2 Answers2