PHP and SQL: managing log-in via databases

Question

I'm creating a website which has a section dedicated to reviews and another one dedicated to users (log-in and sign up), both managed via databases.

In the reviews section, a user can give a review (via a form) which is uploaded in the database using this PHP code

<?php
    if(isset($_POST['pulsanteRecensione']))
    {
        $host = "localhost";
        $username = "root";
        $password = "root";
        $db_nome = "ristorante";
        $tab_nome = "recensioni";

        $link = mysqli_connect($host, $username) or die ('Impossibile connettersi: '.mysqli_error());
        mysqli_select_db($link, $db_nome) or die ('Accesso non riuscito');

        $nome = $_POST['nome'];
        $recensione = $_POST['recensione'];

        $sql = "INSERT INTO $tab_nome (`Nome`, `Recensione`) VALUES ('$nome', '$recensione')";
        if(mysqli_query($link, $sql))
        {
            echo "<h4 align=\"center\">Inserimento avvenuto con successo</h4>";
        }
        else
        {
            echo "<h4 align=\"center\">Spiacenti, inserimento non riuscito</h4>";
        }
    }
?>

and it works. In the same way, I want to manage the users section, so I tried this PHP code for signing up that is more or less the same as the previous one

<?php
    if(isset($_POST['effettuaRegistrazione']))
    {
        $nome = $_POST['nome'];
        $cognome = $_POST['cognome'];
        $mail = $_POST['email'];
        $password = $_POST['password'];
        $data = $_POST['dataNascita'];
        $citta = $_POST['citta'];

        $host = "localhost";
        $username = "root";
        $password = "root";
        $db_nome = "ristorante";
        $tab_nome = "utenti";

        $link = mysqli_connect($host, $username) or die ('Impossibile connettersi: '.mysqli_error());
        mysqli_select_db($link, $db_nome) or die ('Accesso non riuscito');

        $sql = "INSERT INTO $tab_nome (`ID_Utente`, `Cognome`, `Nome`, `E-mail`, `Password`, `Data di nascita`, `Citta`) VALUES ('3','$cognome','$nome','$mail','$password','$data','$citta')";

        if(mysqli_query($link, $sql))
        {
            echo "<h4 align=\"center\">Inserimento avvenuto con successo</h4>";
        }
        else
        {
            echo "<h4 align=\"center\">Spiacenti, inserimento non riuscito</h4>";
        }
    }
?>

but it doesn't work, it always shows Spiacenti, inserimento non riuscito. What am I doing wrong?

Here there is the structure of the utenti table

Can you manually run the query you think you're running? Can you log the query to make sure it looks like what you think it should? Incredibly insecure code of course. — Matt
– Matt, Commented Mar 24, 2017 at 17:34
Rather than echo "<h4 align=\"center\">Spiacenti, inserimento non riuscito</h4>"; check for the real error if any with mysqli_error($link) and use php's error reporting. — Funk Forty Niner
– Funk Forty Niner, Commented Mar 24, 2017 at 17:34
ID_Utente is defined as int, so get rid of the ' in your query around the value. AND password is defined as int. Sure about that?? — Jeff
– Jeff, Commented Mar 24, 2017 at 17:35
and you redefine the user's password when setting the db-password... — Jeff
– Jeff, Commented Mar 24, 2017 at 17:37
@Jeff about the quotes around $password; mysql will compensate for it. However, storing integers as passwords, really isn't recommended. I don't know why they're using that. — Funk Forty Niner
– Funk Forty Niner, Commented Mar 24, 2017 at 17:49

Community · Accepted Answer · 2017-05-23 12:10:36Z

4

For one thing, you have an AI'd column (auto_increment).

You need to replace 3 in '3' with '' in VALUES.

mysqli_error($link) on the query would have signaled the error.

You also shouldn't be storing plain text passwords or as integers (see my note about that further down).

Use password_hash() and a prepared statement as you are open to an SQL injection here.

Use error reporting in case your POST arrays fail.

http://php.net/manual/en/function.error-reporting.php

However, your $link = mysqli_connect($host, $username) and mysqli_select_db($link, $db_nome) may be failing here.

Use all four arguments for it and if there is no password for the db required, use '' only.

I.e.:

$link = mysqli_connect($host, $username, '', $db_nome);

If your present method works, then disregard that ^

Another thing; the password column as an int(15), that doesn't seem to make much sense and it is not a secure method.

Password columns are usually varchar and using a minimum 60 length to save a safe hash, such as password_hash(); the manual on password_hash() says that 255 is a good bet.

Also, mysqli_error() requires a db connection for it mysqli_error($link).

You also need to make sure that the columns' lengths are long enough to hold the data. That in itself could fail silently or truncated.

Note:

Your entire code's execution is relying on this conditional statement:

if(isset($_POST['effettuaRegistrazione'])) {...}

If that fails, so will your entire query.

Plus, as stated in comments (by Jeff):

You're using the same variable for $password for both the POST array and the possible password for your db login; you need to change one of those.

edited May 23, 2017 at 12:10

CommunityBot

11 silver badge

answered Mar 24, 2017 at 17:35

Funk Forty Niner

74.2k14 gold badges71 silver badges146 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

maradev21 Over a year ago

Thanks for your answer and please, don't hate me... First of all I can't use '' to indicate an AI (MySQL returns an error) and I don't need a serious password (it's just a school project), anyway thanks for your advices about passwords, I had never heard about password_hash() before (I'm quite new at PHP). Anyway, the error must be in the query because everything is the same as the other code, which works.

maradev21 Over a year ago

I didn't notice I was using the same variable, I fixed this but it doesn't work anyway. I tried write the same query with values instead of variables and it works, so the problem isn't the rest of the code (which can be questionable, but as I said I'm new at PHP).

Funk Forty Niner Over a year ago

@mara6399 lol I won't hate you ;-) Can you use error reporting and mysqli_error($link) on the query to see what it does return as errors? Then let me know what those are. If you can't use '' to replace the '3' with in VALUES, then try removing both the ID_Utente from the query and the '3' to see if that helps. And make sure all POST arrays have values for them.

Funk Forty Niner Over a year ago

@mara6399 just a quick note: I have to leave for a bit but will be back later. I also noticed you have 2 primary keys; one for the ID_Utente and for E-mail - you can only have one primary key. Try removing the one from E-mail. Error checking would have most likely thrown an error about it. I'll ping you when I get back. See if you can make it work in the meantime.

maradev21 Over a year ago

Removing '3' and ID_Utente it works but it doesn't increment, ID becomes 1 even if there is already a record with this value. I tried again the wrong code using mysqli_error($link) but it doesn't give any error, just doesn't upload on the database (it doesn't matter, now it works, more or less...). Thanks a lot

|

Collectives™ on Stack Overflow

PHP and SQL: managing log-in via databases

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related