Is this the correct way to insert data into database with PHP?

Question

I'm trying to insert user input into a database with the following code.

mysql_query("INSERT INTO 'users' ('Email', 'Username', 'Password') VALUES ($email, $username, $password)");

There are no errors, but the database never seems to get the code inserted. Am I doing something wrong?

Here is my entire code, HTML and all

<?php

    DEFINE ('SERVER', 'localhost');
    DEFINE ('PASSWORD', '');
    DEFINE ('USER', 'root');

    $email = $_POST['email'];
    $username = $_POST['username'];
    $password = SHA1($_POST['pass']);

    if(isset('submitted')
    {

    if($email && $username && $password)
    {
        $to = 'email@example.com'
        $subject = 'subject'
        $body = 'there was an error connecting to the db, please check it.'
        $dbconnect = @mysql_connect(SERVER, USER, PASSWORD) or die("NO WORK!");
        $query = "USE practice" 
        mysql_query($query);

        mysql_query("INSERT INTO users (Email, Username, Password)
             VALUES ('$email', '$username', '$password')") or die(mysql_erorr());
    }
    }

?>
<html>

<form action = "" method = "post">
<label>Email Address</label>
<input type="text" name="email" /> <br />
<label>Desired Username</label>
<input type="text" name="username" /> <br />
<label>Password</label>
<input type="password" name="pass" /> <br />
<input type="submit" value="Register"  />
<input type="hidden" name="submitted" value=1 />
</form>

</html>

Check your query if you have errors... mysql_query("INSERT INTO 'users' ('Email', 'Username', 'Password') VALUES ($email, $username, $password)") or die(mysql_erorr()); — rjmcb, Apr 17 '12 at 00:33
Do as @rjmcb said and check for errors, then post them here if you need more help :) — Charlie Sheather, Apr 17 '12 at 00:36
No. `'` is not a valid quote for identifiers (in either MySQL or ANSI syntax). Also, please *use placeholders* :( — , Apr 17 '12 at 00:40
Does retrieval work? Can you get data from the database? Just to make sure your code and the DB are communicating properly. — Jack, Apr 17 '12 at 01:00
you are still not escaping the variables! This is very important (though will not solve your issue I guess). See my answer and [the link I posted](http://stackoverflow.com/a/7043398/684229) for more info. — Tomas, Apr 17 '12 at 07:47

score 2 · Accepted Answer · edited May 23 '17 at 12:06

2

Probably you should also enclose the values in apostrophes, and probably also you shall not use apostrophes for table and field names, but rather backticks ` or nothing in your case!

mysql_query("INSERT INTO users (Email, Username, Password)
             VALUES ('$email', '$username', '$password')")

But also be sure to properly escape the values of these variables! Not only because of SQL injection but mostly just to assure the proper SQL syntax. Imagine user with the name O'Brian - he would have resulted in SQL error.

edited May 23 '17 at 12:06

Community

1
1

answered Apr 17 '12 at 00:32

Tomas

57,621
49
238
373

Unfortunately, it didn't help. Though, after I saw your response I smacked my head for not thinking of it. – Chris Apr 17 '12 at 00:34
@Chris, and probably also you shall not use apostrophes for table and field names, but rather backticks ` or nothing in your case! – Tomas Apr 17 '12 at 00:36
As far as the properly escaped thing, I'm just doing this quickly to remember the way PHP works. After I've got a working application I'll fix it up. – Chris Apr 17 '12 at 00:36
but this is not PHP, this is affecting mysql! Try the query above. – Tomas Apr 17 '12 at 00:39
Yup. Remember also to run the values through mysql_real_escape_string before appending them to the query string. – iWantSimpleLife Apr 17 '12 at 01:14
@iWant, that's what I wrote in my answer too. – Tomas Apr 17 '12 at 07:28

score 0 · Answer 2 · answered Apr 17 '12 at 01:04

You may be getting some errors but not displaying probably due to following line the spell error with mysql_error as mysql_erorr

   mysql_query("INSERT INTO users (Email, Username, Password)
             VALUES ('$email', '$username', '$password')") or die(mysql_erorr());

Just try to fix that and see if you get some database errors so that it will be easy to trace out and fix it.

score 0 · Answer 3 · answered Apr 17 '12 at 01:08

0

Also when declaring namespaces in the mySQL database. You should put backticks ` like this. So

   mysql_query("INSERT INTO users (`Email`, `Username`, `Password`)
         VALUES ('$email', '$username', '$password')") or die(mysql_erorr());

Otherwise, your code looks solid.

answered Apr 17 '12 at 01:08

Chris Cates

101
1
10

There is no issue if we miss the backticks in column names. It dont make much difference in PHP except the standards. – AjayR Apr 17 '12 at 01:11

Is this the correct way to insert data into database with PHP?

3 Answers3