PHP variable into mySQL table: not working?

Question

This is similar to a question I asked a while ago, however this didn't seem to attract any answers so I'll try again.

I have a booking system which you log into, and your username is stored in a cookie named $_COOKIE['ID_my_site']. I have a script which runs after a user has booked their flight which stores all the details into a table (named OABS_customers), which works fine, however the one row which doesn't work is the username row, which adds $_COOKIE['ID_my_site'] into the row.

The table's structure is the following:

Name         Type(Length)    Null     Default

name         varchar(60)     No       noname
address1     varchar(60)     No       noaddress
address2     varchar(60)     No       noaddress2
county       varchar(60)     No       nocounty
postcode     varchar(32)     No       nopostcode
customer     tinyint(4)      No       
seats        varchar(11)     No       0
number       tinyint(4)      No       0
class        varchar(11)     No       noclass
username     varchar(60)     No       nousername

So like I said, everything works fine but the username column, which just comes up with the default value of nousername. This is the code I am using;

$query = "INSERT INTO OABS_customers (username) VALUES ('$user')";

where $user = $_COOKIE['ID_my_site'] (defined before the above script).

If anyone could help out with this issue I would greatly appreciate it.

what is the value of $_COOKIE['ID_my_site'] when you assign it to $user? — dm03514
– dm03514, Commented May 17, 2012 at 17:54
Standard php/sql debugging regime: Print the query and spot-check for obvious errors. Take the query and run it in manually. Still can't find the issue? Make sure you're selecting the right database and the user has the correct privileges. — Mike B
– Mike B, Commented May 17, 2012 at 17:55
debug the $query variable and try to verify the $user by adding isset to it.thanks — Moyed Ansari
– Moyed Ansari, Commented May 17, 2012 at 17:56
Just in case your query is within a function and the $user variable is declared globally outside of the function you need to make sure that you pull it into your function using global $user; — EmmanuelG
– EmmanuelG, Commented May 17, 2012 at 18:12

nkr · Accepted Answer · 2012-07-12 17:32:02Z

1

Never put unescaped, user generated strings into SQL queries. The cookies could be altered by some evil user and this leads to an SQL injection. Use mysql_real_escape_string($user) to escape the string.

Make sure that $_COOKIE['ID_my_site'] is not empty

mysql_query("INSERT INTO OABS_customers (username) VALUES ('". mysql_real_escape_string($user) ."')");

edited Jul 12, 2012 at 17:32

answered May 17, 2012 at 18:45

nkr

3,0587 gold badges33 silver badges39 bronze badges

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

3 Comments

user1233508 Over a year ago

+1 for stressing the importance of SQL Injection. However, please keep in mind that the mysql extension and the mysql_ functions are being deprecated and mysqli or PDO should be used and recommended instead.

nkr Over a year ago

You are right. With mysqli_ you are able to use prepared statements and mysql_real_escape_string() becomes obsolete.

mlazim14 Over a year ago

Thanks so much, it is finally working somewhat now, and of course is more secure. Thank you again!

message · Accepted Answer · 2012-05-17 18:38:58Z

0

Everything is fine with MySQL query...

mysql> INSERT INTO data (username) VALUES ('random');
Query OK, 1 row affected (0.08 sec)

mysql> select * from data;
+----+----------+
| id | username |
+----+----------+
|  1 | random   |
+----+----------+
1 row in set (0.00 sec)

Looks like $COOKIE value is empty, and when you perform query PHP assigns empty string to username value. MySQL see that empty value is passed and writes default value to username column

answered May 17, 2012 at 18:38

message

4,6133 gold badges31 silver badges40 bronze badges

Collectives™ on Stack Overflow

PHP variable into mySQL table: not working?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related