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:

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?

symcbean · Answer 1 · 2022-05-17T12:16:09.983

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.

edited May 17 '22 at 12:16

answered May 16 '22 at 18:39

symcbean

47,736
6
59
94

The list is good overall, just a couple corrections: mysql is all right with double quotes, and explicitly checking the result of the operation is quite a bad practice, see examples in the [manual](https://www.php.net/manual/en/mysqli.query.php) – Your Common Sense May 16 '22 at 18:52
Not sure what you mean by "explicitly checking the result of the operation is quite a bad practice" - I'm saying your code should always check if the value returned by mysqli_connect() or mysqli_query() is false (and deal with that condition) i.e. NOT expliitly checking is IMHO bad practice. I see nothing on that page to suggest other wise. – symcbean May 17 '22 at 12:19
On this page you can see no code that checks if the value returned by mysqli_connect() or mysqli_query() is false (and deals with that condition) – Your Common Sense May 17 '22 at 12:21
"Out of memory, null pointer exceptions, system call failure, database unavailable, network timeout, and hundreds of other common conditions can cause errors to be generated. These errors must be handled according to a well thought out scheme that will provide a meaningful error message to the user, diagnostic information to the site maintainers, and no useful information to an attacker." https://owasp.org/www-community/Improper_Error_Handling – symcbean May 17 '22 at 12:25
Exactly. It has nothing to do with explicitly checking if the value returned by mysqli_connect() or mysqli_query() is false, though. – Your Common Sense May 17 '22 at 12:27
while yours "explicitly checking" 99% of time means "provide a meaningful error message to an attacker, no useful information to the user and nothing at all to the site maintainers" ...which is hard to evict because it's, well, *explicit* – Your Common Sense May 17 '22 at 12:29
Whereas if you let PHP to handle errors implicitly, you can configure or change the desired behavior in a single place. – Your Common Sense May 17 '22 at 12:35
Sorry but this does NOT sound anything like common sense. Are you talking about enabling exceptions in MySQLi? (which is disabled by default prior to 8.1 and needs to be done explicitly). And which needs an event handler to be declared explicitly to avoid the unhandled exception scenario. – symcbean May 17 '22 at 14:03
Yes, of course I am talking about enabling exceptions in MySQLi. And about an error handler to be declared explicitly **once** per application, as opposed to writing a multitude dedicated error handlers for **the every single query**, as your answer *explicitly* suggests, N'est-ce pas? ;) – Your Common Sense May 17 '22 at 14:22

Designly · Answer 2 · 2022-05-16T18:49:14.033

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}!");
}
?>

Please, [don't die](https://stackoverflow.com/a/15320411/285587) — Your Common Sense, May 16 '22 at 18:54

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

2 Answers2