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, Jan 25 '18 at 11:26
@AlivetoDie no change even with nbrDonation+1 instead of (nbrDonation+1) — chimchim, Jan 25 '18 at 11:31
@AlivetoDie data-type is : int and as an example of the value : 594841 — chimchim, Jan 25 '18 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 — Alive to die - Anant, Jan 25 '18 at 11:35
@AlivetoDie that looks like the original SQL, and the quotes around the ID value shouldn't matter, it slows the process down but shouldn't break it. — Martin, Jan 25 '18 at 11:37
**[How to Prevent SQL Injection compromise in MySQL with PHP](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)** — Martin, Jan 25 '18 at 11:38
[How to output MySQL errors in PHP](https://stackoverflow.com/questions/12227626/how-to-display-mysql-error-in-php) — Martin, Jan 25 '18 at 11:39
@AlivetoDie when i use it directly in the database i got no errors — chimchim, Jan 25 '18 at 11:44
@AlivetoDie now when i added the ' around i get UPDATE wp_projects SET nbrDonation=nbrDonation+1 WHERE projectID='' for echo $sql1; so i don't get anything from the URL — chimchim, Jan 25 '18 at 11:53

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

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 '20 at 09:12

Community

1
1

answered Jan 25 '18 at 11:54

Martin

22,212
11
70
132

for echo $sql1; i get UPDATE wp_projects SET nbrDonation=nbrDonation+1 WHERE projectID=00 so no change in column value – chimchim Jan 25 '18 at 12:02
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. – Martin Jan 25 '18 at 12:27
what does `var_dump($_GET);` tell you? – Martin Jan 25 '18 at 12:28
var_dumps gives me the following line: array(1) { ["projectID"]=> string(0) "" } – chimchim Jan 25 '18 at 13:53
Ok so your value `$_GET["projectID"]` has not been set. That is your problem. – Martin Jan 25 '18 at 14:10

score 0 · Answer 2 · answered Jan 25 '18 at 11:40

0

Remove the single quote your projectID

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

Try now.

answered Jan 25 '18 at 11:40

R Palanivel-Tamilnadu India

442
2
7

While good practise, this should not break the update SQL or cause the upate not to occur. – Martin Jan 25 '18 at 11:43

MySQL UPDATE Query PHP doesn't work

2 Answers2

Solution:

How to Prevent SQL Injection compromise in MySQL with PHP