PHP Create not inserting into db and check if exists

  • (2 Pages)
  • +
  • 1
  • 2

20 Replies - 365 Views - Last Post: 01 February 2019 - 10:48 AM Rate Topic: -----

#1 Exceedinglife   User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 202
  • Joined: 01-July 12

PHP Create not inserting into db and check if exists

Posted 30 January 2019 - 09:24 PM

Hello all, I have another project Iím working on a php login and I am inserting a record in the db Iím checking the table to see if it exists or not so there canít be 2 users with the same username but I am working with create the form submits but nothing is inserted. Also appreciate suggestions in code to make my php better.
<?php
    //mySQL database config
require_once "config.php";

// Define all variables and initialize them as 'empty'
$name = $username = $password = $password2 = "";
$nameerror = $usernameerror = $passworderror = $password2error = "";

// Process data when the form is submitted.
if($_SERVER["REQUEST_METHOD"] == "POST") {

    //Name check
    if(empty(trim($_POST["name"]))) {
        $nameerror = "Please enter a name.";
    } else {
        $name = trim($_POST["name"]);
    }
    // Validate 'Username'
    if(empty(trim($_POST["username"]))) {
        $usernameerror = "Please enter a Username.";
    } else {
        // Prepare a SELECT statement.
        $sql = "SELECT userid FROM users WHERE username = :username";

        if($stmt = $pdoConn->prepare($sql)) {
            // Bind variables to prepared statement as parameters
            $stmt->bindParam(":username", $param_username, PDO::PARAM_STR);
            // Set parameters
            $param_username = trim($_POST["username"]);
            // Attempt to execute prepared statement
            if($stmt->execute()) {
                if($stmt->rowCount() == 1) {
                    $usernameerror = "Username is already taken.";
                } else {
                    $username = trim($_POST["username"]);
                }
            } else {
                echo "Something went wrong with SELECT, please try again later.";
            }
        }
        // Close $stmt
        unset($stmt);
    }
    // Validate Password
    if(empty(trim($_POST["password"]))) {
        $passworderror = "Please enter a password.";
    } else if (strlen(trim($_POST["password"])) < 6) {
        $passworderror = "Password must have at least 6 characters.";
    } else {
        $password = trim($_POST["password"]);
    }
    // Validate Confirm Password.
    if(empty(trim($_POST["password2"]))) {
        $password2error = "Please confirm your password";
    } else {
        $pass2 = trim($_POST["password2"]);
        if(empty($password2error) && ($password != $pass2)) {
            $password2error = "Passwords <b>DID NOT</b> match.";
        }
    }
    //Check for inputs on form to continue.
    // Error checks or input checks.
    if(empty($name) && empty($username) && empty($password) && empty($password2)) {
        // Prepare SELECT statement
        $sql = "INSERT INTO  users (name, username, password) " .
               "VALUES (:name, :username, :password)";
        if($stmt = $pdoConn->prepare($sql)) {
            // Bind variables to prepared statement as parameters
            $stmt->bindParam(":name", $param_name, PDO::PARAM_STR);
            $stmt->bindParam(":username", $param_username, PDO::PARAM_STR);
            $stmt->bindParam(":password", $param_pass, PDO::PARAM_STR);
            // Set parameters
            $para_name = $name;
            $param_username = $username;
            $param_pass = password_hash($password, PASSWORD_DEFAULT);
            // attempt to execute the prepared Statement
            if($stmt->execute()){
                header("Location: ../index.php");
            } else {
                echo "Something went wrong with INSERT";
            }

        }
        // Close Statement
        unset($stmt);
    }
    // Close connection
    unset($pdoConn);
}


?>



Is This A Good Question/Topic? 0
  • +

Replies To: PHP Create not inserting into db and check if exists

#2 andrewsw   User is online

  • Stealth IT
  • member icon

Reputation: 6745
  • View blog
  • Posts: 27,775
  • Joined: 12-December 12

Re: PHP Create not inserting into db and check if exists

Posted 30 January 2019 - 11:00 PM

What debugging steps have you taken to discover where it goes wrong, or whether the insert statement is ever reached? What errors are you getting?
Was This Post Helpful? 0
  • +
  • -

#3 andrewsw   User is online

  • Stealth IT
  • member icon

Reputation: 6745
  • View blog
  • Posts: 27,775
  • Joined: 12-December 12

Re: PHP Create not inserting into db and check if exists

Posted 31 January 2019 - 01:30 AM

Progressive Testing
Was This Post Helpful? 0
  • +
  • -

#4 Exceedinglife   User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 202
  • Joined: 01-July 12

Re: PHP Create not inserting into db and check if exists

Posted 31 January 2019 - 12:01 PM

I use Atom IDE and I have tried setting up xdebug. I have not had any luck with being able to debug my php code line by line I'm really not sure why It doesnt work. I can set breakpoints but they do not turn green(activate) in atom and when i run the page it never stops at any breakpoint. I have tried so many different tutorials on trying to get it to work(debugging) but sstill it doesnt work. If i could debug like visual studio i feel like i would be able to do php easier.

I run the page i get no errors. my create read and delete work on the other pages but update does not. I submit the form and it refreshes the page. It does not go to header(location) ...
Was This Post Helpful? 0
  • +
  • -

#5 andrewsw   User is online

  • Stealth IT
  • member icon

Reputation: 6745
  • View blog
  • Posts: 27,775
  • Joined: 12-December 12

Re: PHP Create not inserting into db and check if exists

Posted 31 January 2019 - 12:12 PM

I did previously manage to get php debugging working but decided it wasn't worth the effort. My tutorial doesn't use it.

You have various print statements, can use the network tab of browser's tools, activate and examine error logs, use Fiddler, etc.
Was This Post Helpful? 0
  • +
  • -

#6 ArtificialSoldier   User is offline

  • D.I.C Lover
  • member icon

Reputation: 2241
  • View blog
  • Posts: 6,793
  • Joined: 15-January 14

Re: PHP Create not inserting into db and check if exists

Posted 31 January 2019 - 12:23 PM

Quote

I did previously manage to get php debugging working but decided it wasn't worth the effort.

I almost got Xdebug to work once. In fact, after opening various firewall things and whitelisting some stuff in Snort and whatever else, I may have even hit a breakpoint once in PHPStorm. Then I decided it wasn't worth the effort.

That's probably one of the greatest weaknesses of PHP, frankly. There might be IDEs that bundle a PHP server to do integrated debugging, but it can be major, major pain in the ass to set it up in your actual environment.

The major way I debug in PHP is also using the error log. Set up the options in php.ini to use an error log, or you can also set those options at runtime. Then you can use the error_log function to send anything you want there. e.g.:

error_log(__FILE__ . __LINE__ . ' array contents: ' . print_r($array, true));


It's not a breakpoint where everything stops and you can look at everything, but you can at least have it tell you what's going on. You can also get a backtrace at any point:

http://php.net/manua...g-backtrace.php

I use that also in various classes. If there's an error in a particular class, it's not really helpful to know which line in the class where the error happened, it's probably more helpful to know where it was called from. So if there's an error you can get a backtrace and use that to print a meaningful error message that includes where the class method was called from.
Was This Post Helpful? 1
  • +
  • -

#7 benanamen   User is offline

  • D.I.C Head

Reputation: 34
  • View blog
  • Posts: 234
  • Joined: 28-March 15

Re: PHP Create not inserting into db and check if exists

Posted 31 January 2019 - 12:59 PM

Quote

Iím checking the table to see if it exists or not so there canít be 2 users with the same username


Here is your first problem. DONT!

Set a unique index on the DB column, attempt the insert and then catch the duplicate error if any. If you were to succeed in your current quest you would be building in a race condition (a bug).
Was This Post Helpful? 0
  • +
  • -

#8 ArtificialSoldier   User is offline

  • D.I.C Lover
  • member icon

Reputation: 2241
  • View blog
  • Posts: 6,793
  • Joined: 15-January 14

Re: PHP Create not inserting into db and check if exists

Posted 31 January 2019 - 02:05 PM

You can do both, you're suggesting a race condition where 2 people try to create accounts with the same username at the same time. Even if someone succeeded at doing that, only one of them would be able to log in. Setting a unique index is good, but I wouldn't rely on only that. Otherwise, any database error at all that happens is going to be met with "that username already exists," and that might not be true.
Was This Post Helpful? 0
  • +
  • -

#9 benanamen   User is offline

  • D.I.C Head

Reputation: 34
  • View blog
  • Posts: 234
  • Joined: 28-March 15

Re: PHP Create not inserting into db and check if exists

Posted 31 January 2019 - 02:31 PM

Quote

any database error at all that happens is going to be met with "that username already exists," and that might not be true.


Any any database error at all? I would kindly ask you to explain what you are saying.
Was This Post Helpful? 0
  • +
  • -

#10 ArtificialSoldier   User is offline

  • D.I.C Lover
  • member icon

Reputation: 2241
  • View blog
  • Posts: 6,793
  • Joined: 15-January 14

Re: PHP Create not inserting into db and check if exists

Posted 31 January 2019 - 02:51 PM

Right, any database error at all. If there's a foreign key constraint, and a unique index, you would want to check for all of those individually to show the correct error message before trying to insert and catching any database error. Hell, maybe it couldn't reach the database server that second for some reason. There could be any number of issues that you might want to respond with "there was a database problem" instead of assuming it's one of many potential causes. That's what I'm saying, it's good to have the appropriate keys and indexes set up but that doesn't mean it's user-friendly to skip explicitly checking for the various issues.

An obvious example might be unique indexes on both username and email, and maybe allowing null for emails. You wouldn't want to always say the username is taken.
Was This Post Helpful? 0
  • +
  • -

#11 benanamen   User is offline

  • D.I.C Head

Reputation: 34
  • View blog
  • Posts: 234
  • Joined: 28-March 15

Re: PHP Create not inserting into db and check if exists

Posted 31 January 2019 - 03:08 PM

To expound, I am saying do not say the username and/or email is taken. Just attempt the insert. If there is any database error, whatever it may be, you catch it in a try/catch and handle it there. For one thing, explicitly saying a specific username/email is not available is a security risk. You would be verifying to an attacker that 50% of the login credentials are correct. Another thing on checking for a user/email first is that you are using twice as many queries as needed to do the same exact job, register a user.

This post has been edited by benanamen: 31 January 2019 - 03:09 PM

Was This Post Helpful? 0
  • +
  • -

#12 Exceedinglife   User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 202
  • Joined: 01-July 12

Re: PHP Create not inserting into db and check if exists

Posted 31 January 2019 - 04:13 PM

thank you I will try with the error log. that is a really good idea I never knew so many people didnt use php debugging I was echoing throughout the code seeing what is happening and that helped alot. I started working on 1 error i have in my other project its literally the last thing and this project is finished. Its my UPDATE. I have create read and delete all working. only update does not update the row in the db.
My page continuously just keeps spinning on my btn click. 
In the network tab it opens a new tab (my php) and it has a yellow circle for google chrome - Provisional headers are shown
Specifically lines 42 - 62 area is where my question lies
In my form the correct data is shown - in form data. So its getting the correct values.

idk if this is anything. 

General 

Request URL:
http://localhost/php...pdate.php?id=13
Referrer Policy:
no-referrer-when-downgrade

This post has been edited by Exceedinglife: 31 January 2019 - 05:02 PM

Was This Post Helpful? 0
  • +
  • -

#13 ArtificialSoldier   User is offline

  • D.I.C Lover
  • member icon

Reputation: 2241
  • View blog
  • Posts: 6,793
  • Joined: 15-January 14

Re: PHP Create not inserting into db and check if exists

Posted 31 January 2019 - 04:41 PM

Quote

If there is any database error, whatever it may be, you catch it in a try/catch and handle it there.

If what you're suggesting is instead of this:

check for errors
if no errors, insert

to do this:
try insert
if there was an error, check the individual fields


Then I can understand that. It sounded like you were suggesting to just try the insert, and if it doesn't work, tell them "something didn't work" or assume it was a specific thing.

Quote

For one thing, explicitly saying a specific username/email is not available is a security risk.

That doesn't really hold water for me. If someone enters a duplicate username, and you detect that, and you don't want to tell them the username already exists because you think that's a security risk, what exactly do you want to tell them? If they know your system, wouldn't they be able to learn what it does if a username is already taken? I don't see how you square not telling someone that a username already exists and also not allowing duplicate usernames. They're going to figure it out.... just give a meaningful message.

Moreover, I don't feel like it is my responsibility as a programmer to protect people who use the same password everywhere. That's their problem, not mine. If another site gets compromised and their password is leaked, and someone uses that to log in to their account on my site, that's not my fault. I'm not going to make it harder on my own users to protect people who don't protect themselves.

The same goes for usernames, for that matter. If someone has the same username that they use on all of their professional sites, plus the porn and cheating husbands sites, again, not my problem. I'm going to say that BobLoblawJun-6-1971 is already registered, if that guy again wants to not protect himself I'm not going to make it harder on my users who are trying to figure out how to sign up by making them guess what the problem is.

Quote

Another thing on checking for a user/email first is that you are using twice as many queries as needed to do the same exact job, register a user.

That's a good point, I agree with trying to insert first and then figuring out what the specific problem was, and telling them. I don't agree that denying a certain user exists is even helpful for security (other than if someone is specifically trying to figure out if someone exists, in which case it's probably a "sensitive" site, in which case why is it my responsibility to make sure they aren't using a username/email that they've never used anywhere else?).

Quote

Its my UPDATE. I have create read and delete all working. only update does not update the row in the db.

So, figure out why. Verify the values that the if statements are checking. Maybe you have an incorrect assumption. If the SQL query is failing, get the error message from the database server and put it in the log. Unless you changed your PDO object to use exceptions, it's not going to show error messages unless you check for them.
Was This Post Helpful? 0
  • +
  • -

#14 Exceedinglife   User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 202
  • Joined: 01-July 12

Re: PHP Create not inserting into db and check if exists

Posted 31 January 2019 - 04:44 PM

UPDATE is solved I figured it out
I went line by line echoing sections to see what was going on and after going through all my php i found the small errors i had! Now I will do that with my create for my login project which is what this topic was initially for.

Thank you everyone you all made alot of good solid points.

1 thing.
So I shouldnt check if a user exists because its doing double the queries first? I should try the insert and if it fails then check to see why if failed? i would think a single select query wouldnt be that hard of a query for a website seems pretty simple

This post has been edited by Exceedinglife: 31 January 2019 - 04:50 PM

Was This Post Helpful? 0
  • +
  • -

#15 ArtificialSoldier   User is offline

  • D.I.C Lover
  • member icon

Reputation: 2241
  • View blog
  • Posts: 6,793
  • Joined: 15-January 14

Re: PHP Create not inserting into db and check if exists

Posted 31 January 2019 - 05:02 PM

Quote

i would think a single select query wouldnt be that hard of a query for a website seems pretty simple

It's not, and the database is optimized specifically for that (even moreso if your username is an index, which it should be). It just might not be necessary, so why make the server do more work than what is necessary?

It's not a problem for a small site. Problems like that happen when your site scales up to many users.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2