0

These days I'm switching from a Windows system to Linux. I installed the LAMP stack, and apparently there seems to be no malfunctions. To be sure I wrote a trite script that connects to the database and performs a simple select query.

phpconnect.php:

<html>
<head>
    <title>Test PHP Connection Script</title>
</head>
<body>

    <h3>PHP Connect Test</h3>

<?php
    $dbname = 'test';
    $dbuser = 'my_user';
    $dbpass = 'my_password';
    $dbhost = 'localhost';

    // connection to db
    $conn = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

    // check connection
    if ($conn->connect_errno) {
        echo "Failed to connect to MySQL: " . $conn->connect_error;
        exit();
    }

    $sql = "SELECT * FROM user";
    $result = $conn->query($sql);

    $row = $result->fetch_array();
    printf("%s %s (%s)\n", $row["firstname"], $row["lastname"], $row["age"]);

output: enter image description here

I checked log file in /var/log/apache2/error.log and this is the error that comes up:

PHP Fatal error: Uncaught Error: Call to a member function fetch_array() on bool in /var/www/html/phpconnect.php:28\nStack trace:\n#0 {main}\n thrown in /var/www/html/phpconnect.php

Sergio
  • 31
  • 9
  • a 404 error means "Not Found". It means your webserver couldn't find the file you requested in the URL. It has nothing to do with your database. – ADyson Sep 24 '21 at 10:40
  • However your screenshot shows a 500 error, not a 404! Please make your sure question is consistent! – ADyson Sep 24 '21 at 10:40
  • Anyway, 500 Internal Server Error is a generic error message informing you that the server crashed while processing the request. Beyond that, it's (intentionally) meaningless, and is of very little use for debugging. You need to check the error logs on the server to try and find the underlying exception message. Once you've got that, you stand a chance of identifying the problem. See [How can I get useful error messages in PHP?](https://stackoverflow.com/questions/845021/how-can-i-get-useful-error-messages-in-php) for guidance. – ADyson Sep 24 '21 at 10:41
  • 2
    But... why are you using the obsolete `mysql_` code library? It was discontinued in PHP 5.5 (2013) and removed entirely in PHP 7 (2015). No apps should still be using it. It leaves you vulnerable to SQL injection attacks (due to the lack of parameterised query support) and potentially other unpatched vulnerabilities. Switch to using `mysqli` or `PDO` (both released in the 2000s!) as soon as possible, and learn how to write parameterised queries to protect your data. See http://bobby-tables.com for a simple explanation of the risks and some sample PHP code to write queries safely. – ADyson Sep 24 '21 at 10:41
  • Also, if you are running `mysql_` queries _successfully_ (which admittedly may not be the case here, but the error message will tell us, once you find it), it shows you are running an unsupported version of PHP. You should upgrade your PHP version urgently to continue to receive security updates. https://www.php.net/supported-versions.php – ADyson Sep 24 '21 at 10:42
  • Also, never configure your web app to login to the database as `root`. Root can do whatever it likes, so on top of any SQL injection vulnerabilities this just leaves your database an open book for hackers. Instead create a separate user account specifically for this application which has only the permissions it actually _needs_ in order to work properly. Don't even use the root account as a shortcut during development or testing, because you need to test your account permissions as well - otherwise when you go live you might have unexpected errors relating to the user account setup. – ADyson Sep 24 '21 at 10:43
  • Thanks for all the suggestions, the shared script was copied from another website changing only the parameters to access the database, I did not noticed that I was using ```mysql_``` . In my project I used ```PDO``` for database connection. As you suggested I will create a user account specifically to this application, but I still do not understand why this error occurs on my Linux OS and not Windows. – Sergio Sep 24 '21 at 16:39
  • It's probably more likely to be related to the PHP version or to some application configuration than the operating system I would guess. We still don't know what the error actually is though - did you go and look in the log (or switch on on-screen error reporting)? – ADyson Sep 24 '21 at 16:46
  • @ADyson I edited the post by removing some unnecessary information. I also modified the code using ```mysqli_``` rather than ```mysql_``` and created a new user instead of using ```root``` as you suggested. Finally, I added the error that comes out in the ```error.log``` file. – Sergio Sep 25 '21 at 09:14
  • 1
    That error means your query failed for some reason. (This is clear because, as per the [manual](https://www.php.net/manual/en/mysqli.query.php), mysqli_query returns `false` (which is a bool) when it fails. Therefore in your code `$result` is `false` and you can't call a function on false! You also need to enable mysqli error reporting so that you get the underlying error message. Add `mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);` before the `new mysqli` line to enable that. Then you'll get a much more useful error message. See also https://stackoverflow.com/a/58808333/5947043 – ADyson Sep 25 '21 at 09:31
  • 1
    @ADyson Done as you said and I checked ```error.log```. The problem was about user privileges, now that snippet works. Anyway I still have problems with my project, but I think is better to open a new post as I have to share more code. – Sergio Sep 25 '21 at 10:19

1 Answers1

1

I answer my question in case someone with the same problem is looking for the solution. Thanks to @ADyson for his help finding a solution, read his comments for details.

The problem arose from the fact that the new user created in mysql did not have the required privileges to access the database tables. For this reason, when the statement that launched the query was reached, it returned a boolean value, that is false. It was enough to modify the user's privileges in relation to the database to be queried. You can do this by running the following command:

mysql> GRANT ALL ON database.table TO 'username'@'localhost';

Where:

  • database is the database name; you can write * to refer to all databases (not recommended)
  • table is the table name; you can also write * to refer to all tables in the selected database

After that, running the command FLUSH PRIVILEGES; will reload the grant tables in the mysql database enabling the changes to take effect without reloading or restarting mysql service.

mysql> FLUSH PRIVILEGES;

Sergio
  • 31
  • 9