MySQL UPDATE Query PHP doesn't work

Question

I'm trying to update a column, I got no modification in the column value can you please help me with that?

Code i am trying:-

global $wpdb;
$param1 = $_GET['projectID'];
$sql1 = "UPDATE wp_projects SET nbrDonation = nbrDonation+1 WHERE projectID = $param1";
$wpdb->query($sql1);
echo $param1;
echo $sql1;

this is what i got as error :

Erreur de la base de données WordPress : [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 '' at line 1] UPDATE wp_projects SET nbrDonation=nbrDonation+1 WHERE projectID=

UPDATE wp_projects SET nbrDonation=nbrDonation+1 WHERE projectID=

Your code is wide open for SQL injection please use prepared statements. What are the errors you get? — SuperDJ
– SuperDJ, Commented Jan 25, 2018 at 11:26
@AlivetoDie no change even with nbrDonation+1 instead of (nbrDonation+1) — chimchim
– chimchim, Commented Jan 25, 2018 at 11:31
@AlivetoDie data-type is : int and as an example of the value : 594841 — chimchim
– chimchim, Commented Jan 25, 2018 at 11:32
ok change query like this:- $sql1="UPDATE wp_projects SET nbrDonation=nbrDonation+1 WHERE projectID=$param1"; and check worked or not. If not just after this query write echo $sql1; and see what query you got printed. Use that query into db directly and see what error you got — Death-is-the-real-truth
– Death-is-the-real-truth, Commented Jan 25, 2018 at 11:35

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

It seems like your $param1 value may be empty, or otherwise invalid.

[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 '' at line 1]

The '' implies that the value is empty; so the SQL is doing:

UPDATE wp_projects SET nbrDonation=(nbrDonation+1) WHERE projectID=''

Which is invalid as nothing ('') is not an integer value as expected.

Solution:

You need to force the $param1 value to be interger. You can do this by typecasting in PHP.

so:

$param1 = (int)$_GET['projectID']; // forces it to a numeric value, 1 or 0

This will then mean the SQL will work correctly:

$sql1 = "UPDATE wp_projects SET nbrDonation = nbrDonation+1 WHERE projectID = $param1";

You do not need the brackets around the nbrDonation+1 and you do not need quotes around the ID number, because it's numeric.

Please also note:

How to Prevent SQL Injection compromise in MySQL with PHP

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jan 25, 2018 at 11:54

Mm-Art-In

23k13 gold badges81 silver badges144 bronze badges

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

5 Comments

chimchim Over a year ago

for echo $sql1; i get UPDATE wp_projects SET nbrDonation=nbrDonation+1 WHERE projectID=00 so no change in column value

Mm-Art-In Over a year ago

so your $param1 is being set as zero; so you're updating a column with id zero which is usually not a column. What should the column id be? You need to check your $_GET value is correct.

Mm-Art-In Over a year ago

what does var_dump($_GET); tell you?

chimchim Over a year ago

var_dumps gives me the following line: array(1) { ["projectID"]=> string(0) "" }

Mm-Art-In Over a year ago

Ok so your value $_GET["projectID"] has not been set. That is your problem.

R Palanivel-Tamilnadu India · Accepted Answer · 2018-01-25 11:40:16Z

0

Remove the single quote your projectID

$sql1="UPDATE wp_projects SET nbrDonation=(nbrDonation+1) WHERE projectID=$param1";

Try now.

answered Jan 25, 2018 at 11:40

R Palanivel-Tamilnadu India

4522 silver badges7 bronze badges

1 Comment

Mm-Art-In Over a year ago

While good practise, this should not break the update SQL or cause the upate not to occur.

Collectives™ on Stack Overflow

MySQL UPDATE Query PHP doesn't work

2 Answers 2

Solution:

How to Prevent SQL Injection compromise in MySQL with PHP

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Solution:

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related