MySQL error, I can't see what's wrong

Question

Hello, I am using this code to enter update user permissions on a web app:

$updaters = array();
for ($i = 1; $i <= 24; $i++){    
  if (isset($_POST['permsA['.$i.']']))        
   $updaters[] = '`'.$i.'` = \''.mysqli_real_escape_string($db, $_POST['permsA['.$i.']']).'\'';
  }
$insert = mysqli_query($db,'UPDATE `tbl_perms` SET '.implode(',', $updaters).    'WHERE `userid` = '.$id)or die(mysqli_error($db));

The error I get is from the SQL:

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 'WHERE `userid` = 1' at line 1

I can't seem to be able to rewrite the code to remove the error :(

this is the code to generate permsA

<?
while($i = mysqli_fetch_array($get_perms)){
$pname = $i[pname];
$id = $i[id];
?>
<div><input type="checkbox" tabindex="1" name="permsA[<? echo $id;?>]" value="1" <? if($permissionid[$id] == '1') {echo ' checked="checked" ';}?> /><?echo htmlspecialchars($pname);?></div>
<? } ?>

Here is the exact query that produces the error:

$insert = mysqli_query($db,'UPDATE `tbl_perms` '.$updaters.' WHERE `userid` = '.$id) or die(mysqli_error($db));

Here is what this sql generates:

UPDATE `tbl_perms` SET WHERE `userid` = 1

And here's a variable dump from the variables after POST var_dump($_POST['permsA']):

array(22) { [1]=> string(1) "1" [2]=> string(1) "1" [3]=> string(1) "1" [4]=> string(1) "1" [5]=> string(1) "1" [6]=> string(1) "1" [8]=> string(1) "1" [9]=> string(1) "1" [10]=> string(1) "1" [11]=> string(1) "1" [12]=> string(1) "1" [13]=> string(1) "1" [14]=> string(1) "1" [15]=> string(1) "1" [16]=> string(1) "1" [17]=> string(1) "1" [18]=> string(1) "1" [19]=> string(1) "1" [20]=> string(1) "1" [21]=> string(1) "1" [22]=> string(1) "1" [23]=> string(1) "1" }

Answer 1

Add a space before your WHERE clause but after the apostrophe ' WHERE

Answer 2

Add a space before WHERE

And be sure that $updaters is filled.

---

Try this:

$updaters = array();
for ($i = 1; $i <= 24; $i++){    
  if (isset($_POST['permsA['.$i.']']))        
   $updaters[] = '`'.$i.'` = \''.mysqli_real_escape_string($db, $_POST['permsA['.$i.']']).'\'';
}
$updaters = (count($updaters) > 0) ? ' Set '.implode (',', $updaters).' ' : '';
$insert = mysqli_query($db,'UPDATE `tbl_perms` '.$updaters.' WHERE `userid` = '.$id)or die(mysqli_error($db));

Answer 3

I'm not certain if it's the problem, but you have no space before the WHERE keyword.

Answer 4

I see 2 potential issues:

• Put a space before WHERE
• your implode function may be leaving a trailing comma which will throw off the query. rtrim(implode(...), ',') to correct

Answer 5

THe answer is already said i guess, but i would advise you to you parameterised queries since they're safer and spare you the trouble of string debugging.

Apperently it is not fixed. Can you post the whole query that is executed?

Answer 6

If you declared your post field name like so: name="permsA[$i]" then you have to access it this way: $_POST['permsA'][$i].

Answer 7

There looks to be a logic error with how you reference $_POST values. When you name input variables with a square brace suffix (e.g. "permsA[]", "permsA[3]"), PHP parses them as arrays. Try:

for ($i = 1; $i <= 24; $i++){    
    if (isset($_POST['permsA'][$i])) {
       $updaters[] = "`$i`='".mysqli_real_escape_string($db, $_POST['permsA'][$i])."'";
    }
}

Or, better yet, use PDO and prepared queries:

// keys of $permsAFields are keys we want to allow for input array 'permsA' 
static $permsAFields = array_fill(1, 24, 1);
...
// filter permsA
$permsA = array_intersect_key($_POST['permsA'], $permsAFields);
if (count($permsA)) {
    $query = $db->prepare("UPDATE `tbl_perms` SET `" . implode('`=?, `', array_keys($permsA)) . "`=? WHERE `userid`=?");
    $permsA[]=$id;
    $query->execute($permsA);
} else { // invalid input
    ...
}