Php not updating table info but query works when executed alone, so do post method and mysqli_connect

Question

<?php
$host_name = '***';
  $database = '***';
  $user_name = '***';
  $password = '***';

$link = mysqli_connect($host_name, $user_name, $password, $database);
$con = $_POST['User_ID'];
echo "Se ha ascendido al usuario $con";
$meta= 'a:1:{s:13:"administrator";b:1;}';
$consulta = 'UPDATE ***usermeta 
       SET 
       meta_value = $meta
       WHERE
       User_ID=$con and meta_key = "***capabilities"';

mysqli_query($link, $consulta);

echo "<br><br><br><a href='***'>Volver</a>";

In this code im trying to update an specific column from a table but it just wont work, it appears like it is working but when i go into phpmyadmin the data wont update, here is some info to keep in mind:

1. mysqli_connect works
2. query works when i execute it on phpmyadmin
3. i can do other queries (select) that works
4. data is correctly received by POST method
5. those " from variable $meta have to stay

I honestly dont have any idea of what is causing the code to just not work, not a single syntax error displayed or anything else. At first i thought it had something to do with the quote marks but now i dismissed that posibility.

Any help?

Answer 1

There's a catalog of issues here.

Your update statement is wrapped in single quotes - so your variables will not be substituted.

You've used double quotes as a delimiters for strings inside the query - that's not supported by SQL - they should be single quotes.

Table names cannot cannot contain asterisk characters.

That you are not seeing "a single syntax error" is a major issue - the DBMS will be screaming for help when it sees this.

Embedding composite data (json) in a scalar value is just asking for trouble.

Your code is vulnerable to SQL injection.

Whenever your thread of execution leaves PHP (in your code, when you call mysqli_conect() and mysqli_query()) you should be explicitly checking the result of the operation.

Answer 2

For one, you should have some kind of error handling so you know what the problem is. Secondly, you're calling mysqli_query directly instead of using it as a method from your already instantiated class $link.

Also, you really should be using back-ticks for column names and single quotes for column values.

Lastly, you need to escape certain special characters using mysqli_real_escape_string. Alternatively, you could use prepared statements, but I'll keep it simple. Instead of prepared statements, you can use PHP's sprintf function.

<?php
$host_name = '***';
$database = '***';
$user_name = '***';
$password = '***';

$link = mysqli_connect($host_name, $user_name, $password, $database);
$con = $_POST['User_ID'];
echo "Se ha ascendido al usuario $con";
$meta= 'a:1:{s:13:"administrator";b:1;}';
$consulta = "UPDATE `usermeta` 
     SET 
     `meta_value` = '%s'
     WHERE
    `User_ID`='%s' and `meta_key` = 'capabilities'";

$consulta = sprintf(
    $consulta,
    esc($meta),
    esc($con)
);

$link->query($consulta);

echo "<br><br><br><a href='***'>Volver</a>";

function esc($v)
{
    global $link;
    return $link->real_escape_string($v);
}
?>

Not sure what the asterisks are in the table name, but they shouldn't be there. Also, note that I created a function for handling escaping for brevity.

EDIT:

For error handling, you should check $link->error.

Example:

<?php
$dbError = $link->error ?? null;

if (!empty($dbError))
{
   die("A database error occurred: {$dbError}!");
}
?>