Insert Mysql row with default values

Question

I need serious help my my sql statement. First, for some reason the default values for bio and user_image are not inserting. Second, my statement works on and off. Sometimes it inserts, sometimes it doesn't. I need some smart person help. Thanks in advance. Heres my code:

$query = "INSERT INTO users VALUES ('{$_POST['display_name']}', '{$_POST['email']}','{$_POST['password']}','active','{$_POST['first_name']}','{$_POST['last_name']}',DEFAULT,DEFAULT)";
mysql_query($query);

echo '<META HTTP-EQUIV="Refresh" Content="0; URL=success.php">';    
    exit;

Messed up I know, im doing this project to learn more about databases. Heres my db structure:

Field      Type                         Null       Default  
id         int(11)                      No       
username   varchar(255)                 No       
email      varchar(255)                 No       
password   varchar(255)                 No       
status     enum('active', 'inactive')   No       
first_name  text                        No       
last_name   text                        No       
bio         varchar(305)                No         HEY! theres nothing here yet ....Complete your bio if you want to get rid of this lame placeholder text. Use this space on your page to tell the internet a little bit about yourself. Or just make everyone feel bad by listing all of your amazing accomplishments! I prefer the latter.    
user_image  varchar(305)                No         user_profile.jpg

**Changed but still not working:

$query = "INSERT INTO users(username, email, password, first_name,last_name)
VALUES ({$_POST['display_name']}, {$_POST['email']}, {$_POST['password']}, {$_POST['first_name']}, {$_POST['last_name']})";

You are missing value for field `id`. And see `mysql_error();` why your query fails. — Glavić, Jan 21 '14 at 22:26
@SharikovVladislav: by the info he has given, we don't know. But it doesn't matter, he must specify values for all fields when he uses INSERT without specifying fields. If field `id` is auto-incremented, then he needs to use `null` as value. — Glavić, Jan 21 '14 at 22:37

score 1 · Answer 1 · edited May 23 '17 at 12:21

Your query is fundamentally unsafe - it can be exploited to insert malicious statements, and POOF! your whole database is gone. You really, really need to read this SO thread and use PDO or mysqli instead, or if that's absolutely not an option at the very least use mysql_real_escape_string.

Having said that, since you're not doing any escaping, any string with a ' in it will break your query. Let's say that $_POST['display_name'] is set to Frank's Restaurant. Your query will then start with INSERT INTO users VALUES ('Frank's Restaurant', and MySQL will think that the query stopped erroneously after Frank, since that's followed by a '.

In addition, as Glavić suggested, you need to add a field for the id, or specify fields manually.

score 0 · Answer 2 · answered Jan 21 '14 at 22:30

Firstly try this:

$query = "INSERT INTO users VALUES ('{$_POST['display_name']}', '{$_POST['email']}','{$_POST['password']}','active','{$_POST['first_name']}','{$_POST['last_name']}',null,null)";

If it wouldn't help try to specify what fields you want to insert. Anyway it is a good practise to do this. Its not very hard, but there are less possibility to get error.

I mean you should try next query:

insert into SomeTable(Field1, Field2, Field5, Field8) values ($field1Data, $field2Data, $field5Data, $field8Data);

Field3, Field4, Field6, Field7 would be default after this.

Insert Mysql row with default values

2 Answers2