PHP Code executing but not inserting data?

Question

I've followed a year old online tutorial of Unity Client - PHP Server - Database integration. The code seems to execute fine, it reaches the 'echo"Success"' line etc perfectly.

However when I look at my database, there is nothing there. Its blank, and I have no idea why.

Note: The online tutorial used mysql... whereas I'm using the (non-depracted) mysqli... but there didn't seem to be that much of a difference, but I'm a total rookie at PHP coding, only having minimal experience at it so it is very possible I'm wrong?

<?php
/**
 * Created by PhpStorm.
 * User: Josh
 * Date: 09/04/2016
 * Time: 14:11
 */

$Username = $_REQUEST["Username"];
$Password = $_REQUEST["Password"];

$Hostname = "localhost";
$DBName = "statemilitaryrpdb";
$User = "root";
$PasswordP = "";

$link  = mysqli_connect($Hostname, $User, $PasswordP, $DBName) or die ("Can't Connect to DB");
if (!$Username || !$Password) {
    echo "Empty";
} else 
{
        $SQL = "SELECT * FROM accounts WHERE Username = '" . $Username ."'";
        $Result = @mysqli_query($link, $SQL) or die ("DB ERROR");
        $Total = mysqli_num_rows($Result);
        if($Total == 0)
        {
            $insert = "INSERT INTO 'accounts' ('Username', 'Password') VALUES ('" .$Username . "', MD5('" . $Password . "'), 0)";
        $SQL1 = mysqli_query($link, $insert);
        $Result2 = @mysqli_query($link, $SQL) or die ("DB ERROR");
        echo(mysqli_num_rows($Result2));
    }
    else
    {
        echo"Username Already Used";
    }
}

mysqli_close($link);

Your insert has two columns, but three values. Also, there's no error checking on the row where you do your insert. — andrewsi
– andrewsi, Commented Apr 9, 2016 at 15:06

Jay Mason · Accepted Answer · 2016-04-09 15:16:14Z

3

$insert = "INSERT INTO 'accounts' ('Username', 'Password') VALUES ('" .$Username . "', MD5('" . $Password . "'), 0)";

Answer: Username and Password are the fields but you are trying to insert Username, Password and 0

Suggestion: Do more than just MD5 encryption, that is SUPER easy to decrypt.

Edit: Also like @andrewsi said in the comments if your only going to check if its empty, than anyone could SQL inject your database and drop your tables or make changes. Make sure that you are filtering your inputs correctly.

edited Apr 9, 2016 at 15:16

answered Apr 9, 2016 at 15:10

Jay Mason

4703 silver badges20 bronze badges

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

1 Comment

andrewsi Over a year ago

You might also want to add a note about using a prepared statement, too - this is currently wide open to SQL injection

Panda · Accepted Answer · 2016-04-09 15:29:24Z

2

Firstly, your query have only 2 columns, but you are inserting 3 values:

$insert = "INSERT INTO 'accounts' ('Username', 'Password') VALUES ('" .$Username . "', MD5('" . $Password . "'), 0)";

Columns

Username
Password

Values to insert

$Username
md5($Password)
0

Thus, not all the values will be inserted.

Secondly, for MySQL related names, you need to use back ticks instead of single-quote.

Thus, this:

INSERT INTO 'accounts'

Should be:

INSERT INTO `accounts`

Thirdly, your code is vulnerable to MySQL Injection, you should prevent it using mysqli_real_escape_string():

$Username = mysqli_real_escape_string($link, $_REQUEST["Username"]);
$Password = mysqli_real_escape_string($link, $_REQUEST["Password"]);

Tip: You shouldn't suppress error messages:

@mysqli_query($link, $SQL)

Remove @ to enable error reporting. It's very useful in diagnosing syntax errors.

Also, you shouldn't use md5() to hash passwords, as it's not very secure. Use password_hash and password_verify instead.

edited Apr 9, 2016 at 15:29

answered Apr 9, 2016 at 15:14

Panda

6,8846 gold badges46 silver badges61 bronze badges

1 Comment

NeoKuro Over a year ago

awesome thank you very much (everyone) for the help, but this answer was the most detailed I felt. I've also never had to deal with security before, as such SQL injection vulnerabilities etc is all new to me...do you have any tips on what else to look out for (as this is the very basic version of the database/server, I'm going to be greatly expanding it soon most likely)

mferly · Accepted Answer · 2016-04-09 15:52:28Z

0

In debug mode, never use @ to suppress errors, ie. @mysqli_query. Also or die("DB ERROR") isn't very descriptive. Even if that resolves, what good does DB ERROR provide you? Instead, use or die( mysqli_error($link) ) to see what's really going on with the query.

You also have 3 values to be inserted, but only 2 columns represented in the query statement:

('Username', 'Password') // 2 columns

VALUES ('" .$Username . "', MD5('" . $Password . "'), 0)"; // 3 values

What column is 0 being inserted into? This value needs to be represented by a column.

And a table/column name should never be wrapped with quotes; only ticks `accounts`

answered Apr 9, 2016 at 15:52

mferly

1,6561 gold badge13 silver badges19 bronze badges

Collectives™ on Stack Overflow

PHP Code executing but not inserting data?

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related