Why isn´t my sql-stament inserting data into my database?

Question

Recently I build a login-system using php and a mysql-database. I entered all users manually so what I want to do next is builging a register-funktion. For some reason however the data don´t land in my database.

This is my register-page:

<div class="login">
    <div class="background">
        <img src="../pic/skyscrapers.jpg"></img>
        <div class="logbox">
            <form class="form-register" action="../func/func_register3.php" method="POST">
                <h1>Registrierung</h1>
                <div class="user">
                    Choose your username:<br>
                    <label for="frm_user">username</label>
                    <input type="text" name="frm_user" id="frm_user" placeholder="Benutzername" required autofocus>
                    <br>
                    <br>
                    Choose your password:<br>
                    <label for="frm_pass">Password</label>
                    <input type="password" name="frm_pass" id="frm_pass" placeholder="Password" required>
                    <br>
                    <br>
                    Repeat your password:<br>
                    <label for="frm_pass_rep">Repetition</label>
                    <input type="password" name="frm_pass_rep" placeholder="Password"><br>
                    <br>
                    <br>
                </div>
                <button name="btn_register" id="btn_register" type="submit">register</button>
            </form>
        </div>
    </div>
</div>

And this the php-file with the register-logic:

<?php

if (isset($_POST['btn_register'])) {
        include("../inc/db_connect.php");

        //Form fields -> php-variables
        $frm_user = $_POST['frm_user'];
        $frm_pass = $_POST['frm_pass'];
        $frm_pass_rep = $_POST['frm_pass_rep'];

        $stmt = $dbh->prepare("INSERT INTO tbl_users (username, password) VALUES (:frm_user, :frm_pass)");
        $stmt->bindParam(':frm_user', $frm_user);
        $stmt->bindParam(':frm_pass', $frm_pass);

    if ($stmt->execute() == true):
        echo "All good!";
        else:
        {
            echo "The statement has not been executed!";
            echo "<br>";
            echo print_r($stmt->errorInfo, true);
            echo "<br>";
            echo $stmt->errorCode();
        }
        endif;
}

The output is "The statement has not been executed! 23000" which is true, there is no data coming through to the database... I already tried using different kinds of browsers and using anonther database. The login works perfektly fine and both files, the one with the login-logik as well as the one with the register-logik, use the same file to connect to the database. I´d be very thankful for all kinds of ideas on how to fix this.

**Never store plain text passwords!** Please use ***PHP's [built-in functions](http://jayblanchard.net/proper_password_hashing_with_PHP.html)*** to handle password security. If you're using a PHP version less than 5.5 you can use the `password_hash()` [compatibility pack](https://github.com/ircmaxell/password_compat). ***It is not necessary to [escape passwords](http://stackoverflow.com/q/36628418/1011527)*** or use any other cleansing mechanism on them before hashing. Doing so *changes* the password and causes unnecessary additional coding. — Jay Blanchard, May 09 '19 at 13:41
Your `$stmt` might be fine but you're not actually checking the return value on `$stmt->execute()` — CD001, May 09 '19 at 13:45
When I first tried, I encrypted the passwords. But since nothing worked, I reduced everything to minimum :D — Greta, May 09 '19 at 13:45
@CD001 It has to be if($stmp->execute() == true)? If I do this, the output is "The statement has not been executed!"... So the error message makes sense. Any ideas on how to get through to the database? — Greta, May 09 '19 at 13:48
Your statement is an object. It won't magically transform into a boolean `false` after a failed query. Yes, you need to check the return value of `execute()`. — deceze, May 09 '19 at 13:48
@all thanks for your help so far! I´m still not getting through to the database... Any ideas rhow to fix this? — Greta, May 09 '19 at 13:51
Try checking for [PDO errors](http://php.net/manual/en/pdo.error-handling.php). There may be a non-obvious issue going on, such as a database restriction or invalid column name. — aynber, May 09 '19 at 13:56
@aynber The exceptions in the file that builds the db connection is already handled. When I implement a try-catch-block to the register logic, the message remains ""The statement has not been executed!"... Or is the try-catch needed somewhere else? — Greta, May 09 '19 at 14:06
`echo "The statement has not been executed: " . print_r($stmt->errorInfo, true);` to get the actual error associated — Chris White, May 09 '19 at 14:07
While checking for an error is good, the key is to find out what the actual error is, as shown by Chris's comment. — aynber, May 09 '19 at 14:11
Can you give at least one reason why you are calling execute twice? — Your Common Sense, May 09 '19 at 14:16
@ChrisWhite, thanks for the idea. Just tried and didnt get any error message... — Greta, May 09 '19 at 14:18
@ChrisWhite PDO reports errors by itself, no need to write any additional code. Least it should be a code outputs the error directly. See https://phpdelusions.net/articles/error_reporting — Your Common Sense, May 09 '19 at 14:19
@YourCommonSende The second time I write it is inside an if... — Greta, May 09 '19 at 14:21
`$stmt->execute();` Executes the statement. `if($stmt->execute() == true):` executes the statement again and checks to see if it was successful. If you have a unique constraint on your username, the if statement will fail. Remove the first execute so that the second will pass/fail on its own. — aynber, May 09 '19 at 14:24

score -1 · Answer 1 · answered May 09 '19 at 13:44

-1

I guess that your id column is an autoincrement. You should remove it from your query:

$stmt = $dbh->prepare("INSERT INTO tbl_users (username, password) VALUES (:frm_user, :frm_pass)");

or if is not autoincrement or you want to give it a value you should insert a value for it:

$stmt = $dbh->prepare("INSERT INTO tbl_users (id, username, password) VALUES (:id,:frm_user, :frm_pass)");

answered May 09 '19 at 13:44

nacho

5,280
2
25
34

1

*I would recommend you* to not *answer* questions with this many leaks in them. The answer might be correct but maybe put some effort in to make it secure. – Tomm May 09 '19 at 13:46
1

This answer no longer reflects the post contents. Id is not referenced anywhere in the query. – aynber May 09 '19 at 13:57
@nacho Yes, the id column is an autoincrement. I just removed it, gave it another try but got the same result... To give you further information about the table, there are fours columns. "id" of type int(11) is the primary key and autoincremented, "username" of type varchar(25), "password" of type varchar(22) and "admin" of type tinyint(1). Admin is set to 0 per default meaning that the user doen´t have admin rights. – Greta May 09 '19 at 13:59
*"I guess"* - don't guess. – Jay Blanchard May 09 '19 at 14:33

Why isn´t my sql-stament inserting data into my database?

1 Answers1