MySQL error code 0 in PHP? Cannot insert data into database with PHP

Question

So trying to insert some data from a PHP page into my SQL database. This page is ONLY accessible via myself so I'm not worried about it being accessed or SQL injectable etc. My issue is no matter what code I use it doesn't go into the database. I've tried coding it myself, using template codes, taking from php.net etc nothing has worked!

It now redirects me with the success message but still nothing in the database.

Code will be put below and I'll edit some of my details for privacy reasons.

<?php
require connect.php

   // If the values are posted, insert them into the database.
    if (isset($_POST['username']) && isset($_POST['password'])){
        $username = $_POST['username'];
        $isadminB = $_POST['isadmin'];
        $password = $_POST['password'];

        $query = "INSERT INTO `users` (user_name, password, isadmin) VALUES ('$username', '$password', '$isadminB')";
        $result = mysql_query($query);
        if($result){
            $msg = "User Created Successfully.";
        }
    }
    $link = mysql_connect("localhost", "root", "password");
echo mysql_errno($link) . ": " . mysql_error($link). "\n";

The echo mysql_errno($link) . ": " . mysql_error($link). "\n"; was the code that gave me error code 0?

As requested the code for the form from my previous page.

<form action="account_create_submit.php" method="post">
Username: <input type="text" name="username" id="username"> <br /><br />
Password: <input type="password" name="password" id="password"> <br /><br />
<span id="isadmin">Is Admin: Yes<input type="radio" name="isadmin" id="1" value="1"> | No<input type="radio" name="isadmin" id="0" value="0"><br /></span>
<span id="submit"><input type="submit" value="Create Account"></span>
</form>

Ok so changed the form code so method is now POST. Great! All data is being read correctly although that wasn't my issue as even typing in hard data for the code to submit wasn't working at least its a future issue resolved already. The new error code is no longer 0 but rather the following:

1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''user_name', 'password', 'isadmin') VALUES ('testZ', 'lol', '1')' at line 1

Connect.php

    <?php
$connection = mysql_connect('localhost', 'root', 'password');
if (!$connection){
    die("Database Connection Failed" . mysql_error());
}
$select_db = mysql_select_db('Default_DB');
if (!$select_db){
    die("Database Selection Failed" . mysql_error());
}

Answer 1

Firstly, for those of you getting the misconception about password for a column name:

Sure, it's MySQL "keyword", but not a "reserved" word; more specifically, it is a function (see ref). Notice there is no (R) next to the "function (keyword) name": https://dev.mysql.com/doc/refman/5.5/en/keywords.html therefore it's perfectly valid as a column name.

Ref: https://dev.mysql.com/doc/refman/5.1/en/encryption-functions.html#function_password

Ticks are only required if it is used in order to prevent it from being recognized as a "function", which it clearly is not in the OP's case. So, get your information and facts straight.

More specifically, if a table named as PASSWORD and without spaces between the table name and the column declaration:

I.e.: INSERT INTO PASSWORD(col_a, col_b, col_c) VALUES ('var_a', 'var_b', 'var_c')

which would throw a syntax error, since the table name is considered as being a function.

Therefore, the proper syntax would need to read as

INSERT INTO `PASSWORD` (col_a, col_b, col_c) VALUES ('var_a', 'var_b', 'var_c')

(Edit:) To answer the present question; you're using $connection in your connection, but querying with $link along with the missing db variables passed to your query and the quotes/semi-colon I've already outlined here.

That's if you want to get that code of yours going, but I highly discourage it. You're using a deprecated MySQL library and MD5 as you stated. All old technology that is no longer safe to be used, nor will it be supported in future PHP releases.

You're missing a semi-colon here require connect.php and quotes.

That should read as require "connect.php";

You should also remove this:

$link = mysql_connect("localhost", "root", "password");
echo mysql_errno($link) . ": " . mysql_error($link). "\n";

you're already trying to include a connection file.

Use this in your connection file: (modified, using connection variable connection parameter)

$connection = mysql_connect('localhost', 'root', 'password');
if (!$connection){
    die("Database Connection Failed" . mysql_error());
}
$select_db = mysql_select_db('Default_DB', $connection);
if (!$select_db){
    die("Database Selection Failed" . mysql_error());
}

and pass the $connection to your query as the 2nd parameter.

$result = mysql_query($query, $connection);

Add error reporting to the top of your file(s) right after your opening PHP tag for example <?php error_reporting(E_ALL); ini_set('display_errors', 1); then the rest of your code, to see if it yields anything.

Also add or die(mysql_error()) to mysql_query().

If that still gives you a hard time, you will need to escape your data.

I.e.:

$username = mysql_real_escape_string($_POST['username'], $connection);

and do the same for the others.

Use a safer method: (originally posted answer)

May as well just do a total rewrite and using mysqli_ with prepared statements.

Fill in the credentials for your own.

Sidenote: You may have to replace the last s for an i for the $isadminB that's IF that column is an int.

$link = new mysqli('localhost', 'root', 'password', 'demo');
if ($link->connect_errno) {
    throw new Exception($link->connect_error, $link->connect_errno);
}

if (!empty($_POST['username']) && !empty($_POST['password'])){
    $username = $_POST['username'];
    $isadminB = $_POST['isadmin'];
    $password = $_POST['password'];

// now prepare an INSERT statement
    if (!$stmt = $link->prepare('INSERT INTO `users` 
          (`user_name`, `password`, `isadmin`) 
           VALUES (?, ?, ?)')) {
        throw new Exception($link->error, $link->errno);
    }

    // bind parameters
    $stmt->bind_param('sss', $username, $password, $isadminB);

        if (!$stmt->execute()) {
            throw new Exception($stmt->error, $stmt->errno);
        }

    }
    
    else{
        echo "Nothing is set, or something is empty.";
    }

---

I noticed you may be storing passwords in plain text. If this is the case, it is highly discouraged.

I recommend you use CRYPT_BLOWFISH or PHP 5.5's password_hash() function. For PHP < 5.5 use the password_hash() compatibility pack.

You can also use this PDO example pulled from one of ircmaxell's answers:

Just use a library. Seriously. They exist for a reason.

• PHP 5.5+: use password_hash()
• PHP 5.3.7+: use password-compat (a compatibility pack for above)
• All others: use phpass

Don't do it yourself. If you're creating your own salt, YOU'RE DOING IT WRONG. You should be using a library that handles that for you.

$dbh = new PDO(...);

$username = $_POST["username"];
$email = $_POST["email"];
$password = $_POST["password"];
$hash = password_hash($password, PASSWORD_DEFAULT);

$stmt = $dbh->prepare("insert into users set username=?, email=?, password=?");
$stmt->execute([$username, $email, $hash]);

And on login:

$sql = "SELECT * FROM users WHERE username = ?";
$stmt = $dbh->prepare($sql);
$result = $stmt->execute([$_POST['username']]);
$users = $result->fetchAll();
if (isset($users[0]) {
    if (password_verify($_POST['password'], $users[0]->password) {
        // valid login
    } else {
        // invalid password
    }
} else {
    // invalid username
}

Answer 2

You are using "get" as your form submission method. "post" variables won't be recognized.

Also...

It looks like you're missing the second parameter of your mysql_query() function which is your link identifier to the MySQL connection. I'm assuming you've created the connection in connection.php.

Typically, the mysql_query() function would be

$result = mysql_query($query, $conn);

with $conn having been pre-defined in your connection.php file.

Answer 3

Why are you putting all the information from the form in the link on submit? ex: account_create_submit.php?username=myusername&password=mypassword&isadmin=0

---

I can see that $username = $_POST['username']; doesn't match the username in your query string.

$query = "INSERT INTOusers(user_name, password, isadmin) VALUES ('$username', '$password', '$isadminB')";

While your fixing that why don't you just make $isadminB and $_POST['isadmin'] the same. Use 'isadminB' in both places.

Check that out and see what happens!

Answer 4

password is a special word in MySQL, and it might be necessary to put the word in quotes like `password`.