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

MylesZ3
May 16, 2022
159 views
0 votes
2 Answers

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

mysqli_connect works
query works when i execute it on phpmyadmin
i can do other queries (select) that works
data is correctly received by POST method
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?

Answers

- symcbean
- May 16, 2022 at 8:39 pm
- 0 votes
0
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.

Login or Signup to reply.

- Designly
- May 16, 2022 at 8:43 pm
- 0 votes
0
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}!");
}
?>
```
Login or Signup to reply.