<?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?

CodePudding user response：

There's a catalog of issues here.

Your update statement is wrapped in single quoes - 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.

CodePudding user response：

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.