Problems with PHP connection -> Mysql

Question

Error

Warning: mysqli_connect() [Function.mysqli-connect]: (HY000/1130): Host '10.1.1.25' is not allowed to connect to this Mysql server in /home/a1570220/public_html/Register.php on line 2

Warning: mysqli_prepare() expects Parameter 1 to be mysqli, Boolean Given in /home/a1570220/public_html/Register.php on line 8

Warning: mysqli_stmt_bind_param() expects Parameter 1 to be mysqli_stmt, null Given in /home/a1570220/public_html/Register.php on line 9

Warning: mysqli_stmt_execute() expects Parameter 1 to be mysqli_stmt, null Given in /home/a1570220/public_html/Register.php on line 10

{"Success":true}

Here the code:

<?php
$con = mysqli_connect("confidencial", "confidencial", "confidencial", "confidencial");

$email = $_POST["email"];
$senha = $_POST["senha"];
$statement = mysqli_prepare($con, "INSERT INTO user (email, senha) VALUES (?, ?)");
mysqli_stmt_bind_param($statement, "siss", $email, $senha);
mysqli_stmt_execute($statement);

$response = array();
$response["success"] = true;  

echo json_encode($response);
?>

Where it says confidential are the private data of my db so I exchanged.

How can I fix this?

Answer 1

The first mistake, HY000/1130 is what matters in the case:

Warning: mysqli_connect() [Function.mysqli-connect]: (HY000/1130): Host '10.1.1.25' is not allowed to connect to this Mysql server in /home/a1570220/public_html/Register.php on line 2

It means that the host you are using for the connection you are not authorized to access the DB.

In other words, even if the user and password are right, it is necessary that in Mysql this user has permission to access the IP you are using.

To see the permissions of the desired user you can do so:

SHOW GRANTS FOR nomeDoUser@ipOuNomeDoHost;

Note that the IP you are using not necessarily the machine where the application comes out, yes the last of the final routing, if it is a NAT, Docker, virtualization or anything that modifies the IP for Mysql. For this, check the final destination the IP that is coming to the machine (IP or host name).

For example, when creating a user with this line:

CREATE USER 'jeffrey'@'localhost' ...

you are allowing the user jeffrey only connect locally, and this if localhost is a locally valid name. It is not always.

CREATE USER 'jeffrey'@'%' ...

here is already using the "joker" %, which allows access from any host, which is not normally desirable if a more restricted permission resolves, especially on a network with fixed Ips (such as a hosting, if DB is only accessed by an application from the same network).

CREATE USER 'jeffrey'@'200.200.200.1' ...

Finally, so you set a single IP for access.

If the user has already been created, you can change the field host in the Mysql user table (or preferably using the change-specific command), to avoid having to create a new user for access.

In addition to the user needing access above, it is necessary to adjust the user access to the tables. For example:

GRANT ALL PRIVILEGES ON *.* TO 'usuário'@'%';

You can give several GRANTs for different Ips with the same user if you want. You can even give permissions that change depending on the IP used.

Heed:

• normally you should not use the above line exactly as it is.

  ALL PRIVILEGES is usually very privileged for a normal system user. The ideal is to allow only the minimum required, for example only SELECT, INSERT etc, i.e., only what is used;

  ON *.* is giving access to all tables and banks, in case you should put only what is necessary. It is better something like: ON db_especifico.* TO...

• you need to execute the above command as administrator.

To effect the above change, finally perform

FLUSH PRIVILEGES;

to update Mysql credentials.

As for the following errors, they are basically a consequence of the initial problem. Are missing in your application the checks before making the query to know if each previous command has been successfully executed.