PHP database getting variables

Question

I have the following line of code that executes properly except for one hitch. It will print the variable name into the database.

I use this code in one of my scrips:

$con = mysql_connect("MyServer","MyDB","myPwd");
mysql_select_db("MyDB", $con);
$sql = "INSERT INTO `MyDB`.`Shipping` (`ID`, `FIRSTNAME`, `LASTNAME`, `ADDRESS1`, `ADDRESS2`, `CITY`, `STATE`, `ZIP`, `ORDERNUMBER`, `SHIPPINGTYPE`, `item1`, `item2`, `item3`, `item4`, `item5`, `item6`, `item7`, `item8`, `item9`, `item10`) VALUES (NULL, \'$first_name\', \'lname\', \'addr\', \'\', \'city\', \'state\', \'zip\', \'ordernum\', \'\', \'0\', \'0\', \'0\', \'0\', \'0\', \'0\', \'0\', \'0\', \'0\', \'0\');";
mysql_query("$sql");
mysql_close($con);

This code prints the followin to the database:

$first_name lname   addr        city    state   zip 0       0   0   0   0   0   0   0   0   0   0

Notice that the variable name, and not the contents of the variable are printed. How can I get it to print out the variable contents?

Beware of SQL injection

cambraca
– cambraca

2012-04-06 21:14:55 +00:00
Commented Apr 6, 2012 at 21:14 — cambraca
– cambraca, Commented Apr 6, 2012 at 21:14

Community · Accepted Answer · 2023-11-17 20:38:18Z

Notice that the variable name, and not the contents of the variable are printed

This is because you are not passing variables to the insert statement, rather you are passing undefined(?) constants; lname, addr, etc. You should pass in the variables you assigned values to: (assuming) $lname, $addr, etc.

Also, you don't need to pass in a NULL for the ID column. If it's set to auto_increment, the database will create this value for you.

You should also take advantage of the default column values defined by your table schema, so you don't end up passing in a huge list of variables with default values, for example, if you have a DEFAULT 0 as part of the schema for item1, item2, item3 etc, you can simply make the following insert:

$sql =
"INSERT INTO `MyDB`.`Shipping` (`FIRSTNAME`, `LASTNAME`, `ADDRESS1`, `CITY`, `STATE`, `ZIP`, `ORDERNUMBER`) VALUES ('$first_name', '$lname', '$addr', '$city', '$state', '$zip', '$ordernum');";

The database will populate item1, item2, etc columns with 0 for you automatically.

As cambraca mentioned, if you pass variables directly into your query like this, you run the risk of SQL injection. You should read up on how to prepare your queries, which will also force you to use mysqli (vs outdated mysql) api. Or even better, read up on PDO.

Cfreak · Accepted Answer · 2012-04-06 21:21:51Z

2

For one you have a quoting issue. You seem to have double quotes and then escaped single quotes. don't escape your single quotes if you string is double quoted.

It seems the value of $first_name should work. Is this the exact code? Because if you have single quotes around the whole thing it would create the output you're describing.

All your other columns aren't variables so it's hard to know what you're trying to do. If you aren't going to use them why reference them at all?

In your mysql_query call don't quote $sql again.

Finally check mysql_error() for errors. It could be you are seeing something old in the database and your code isn't working at all.

Consider using PDO. It has support for placeholders, better security and better debugging than the native mysql_* interface.

answered Apr 6, 2012 at 21:21

Cfreak

19.4k6 gold badges52 silver badges64 bronze badges

1 Comment

Mike Purcell Over a year ago

Agreed. Is a messy situation.

hjpotter92 · Accepted Answer · 2012-04-06 21:17:35Z

0

Change the insertion statement to:

$sql = "INSERT INTO `MyDB`.`Shipping` 
  (`ID`, `FIRSTNAME`, `LASTNAME`, `ADDRESS1`, `ADDRESS2`, `CITY`, 
    `STATE`, `ZIP`, `ORDERNUMBER`, `SHIPPINGTYPE`, `item1`, `item2`, 
    `item3`, `item4`, `item5`, `item6`, `item7`, `item8`, `item9`, `item10`)
  VALUES (NULL, '{$first_name}', 'lname', 'addr', '', 'city', 'state',
    'zip', 'ordernum', '', '0', '0', '0', '0', '0', '0', '0',
    '0', '0', '0');";

Also, do check that your $first_name is properly assigned a value.

answered Apr 6, 2012 at 21:17

hjpotter92

81.1k36 gold badges148 silver badges188 bronze badges

1 Comment

cantlin Over a year ago

Those curly braces will make absolutely no difference whatsoever.

Philipp · Accepted Answer · 2012-04-06 21:25:20Z

0

I suppose not to use inline variables in strings in PHP, and you are escaping ' when you do not need to. Try this:

$con = mysql_connect("MyServer","MyDB","myPwd");
mysql_select_db("MyDB", $con);
$sql = "INSERT INTO `MyDB`.`Shipping` (`ID`, `FIRSTNAME`, `LASTNAME`, `ADDRESS1`, `ADDRESS2`, `CITY`, `STATE`, `ZIP`, `ORDERNUMBER`, `SHIPPINGTYPE`, `item1`, `item2`, `item3`, `item4`, `item5`, `item6`, `item7`, `item8`, `item9`, `item10`) VALUES (NULL, '".$first_name."', 'lname', 'addr', '', 'city', 'state', 'zip', 'ordernum', '', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0');";
mysql_query($sql);
mysql_close($con);

edited Apr 6, 2012 at 21:25

answered Apr 6, 2012 at 21:16

Philipp

1,4451 gold badge12 silver badges23 bronze badges

2 Comments

hjpotter92 Over a year ago

"', '".lname."', '".addr."' hmm :) :thinker:

cantlin Over a year ago

Using concatenation instead of interpolation won't make any difference either.

Michal · Accepted Answer · 2012-04-07 21:31:18Z

0

Rather than using \'$first_name\' replace it with \"$first_name\" and the same follows with other variables too.

edited Apr 7, 2012 at 21:31

Michal

3,3524 gold badges33 silver badges51 bronze badges

answered Apr 6, 2012 at 21:24

aquasan

3843 silver badges7 bronze badges

Collectives™ on Stack Overflow

PHP database getting variables

5 Answers 5

Comments

1 Comment

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related