INSERT INTO sql query is using variable string rather than field name

Question

Getting really confused surrounding this INSERT INTO. It should insert three fields into the table, userID, activateKey and isActivated.

The activateKey is a 25 letter randomly generated key such as 63n20kw24ba1mlox34e8n2awv

The userID comes from another table and is set by auto_increment.

The isActivated is always 0 at this stage.

It seems like quite a simple INSERT statement

if (!mysqli_query($con,"INSERT INTO activations (userID,activationKey,isActivated) VALUES (".$userID.",".$activateKey.",'0')"))
  {
    echo("Error description: " . mysqli_error($con));
  }

However it doesn't work when I include the $activateKey field. What it does is try to search the string variable $activateKey as a column name. The error I get is:

Error description: Unknown column '63n20kw24ba1mlox34e8n2awv' in 'field list'

Of course there is no such column as 63n20kw24ba1mlox34e8n2awv, this is the data I'm trying to insert, hence why it's in the VALUES section. Any ideas why it's trying to search this as the column name?

Edit to clarify: the var is activateKey, the column name is activationKey

Sorry, I'll edit. The correct var is `activateKey`. The column name is `activationKey` - I changed them to be different to try and avoid confusion between the two but I seem to have done the opposite! — Francesca, Oct 13 '14 at 22:36

score 1 · Accepted Answer · answered Oct 13 '14 at 22:42

I would put the query in a different variable to avoid confusion, and PHP automatically substitutes variable names in strings in double quotes.

Try this:

<?php
  $query = "INSERT INTO activations (userID,activationKey,isActivated) VALUES($userID,'$activateKey','0')
  if (!mysqli_query($con,$query)
   {
     echo("Error description: " . mysqli_error($con));
   }

score 1 · Answer 2 · answered Oct 13 '14 at 22:42

You are not surrounding the values with quotes, that's why they get interpreted as variable names. Use single quotes, like this:

"INSERT INTO activations (userID,activationKey,isActivated) VALUES
   ('".$userID."','".$activateKey."','0')"

However, be aware that stringing together query strings exposes you to SQL injection attacks, if that's a concern in your code you should use parameterized queries. In fact, using parameterized queries is always better.

score 1 · Answer 3 · edited May 23 '17 at 11:52

1

Change your query to this:

"INSERT INTO activations 
        (userID,activationKey,isActivated) 
 VALUES ('$userID','$activateKey','0')"

You dont need to use the concatenation (.) operator as variables will be interpolated into the string.

The single quotes tell mysql to treat the variables as literals instead of column names.

As a side note you would be better to use parameterized queries. See How can I prevent SQL injection in PHP?

edited May 23 '17 at 11:52

Community

1
1

answered Oct 13 '14 at 22:43

andrew

9,313
7
30
61

Thanks. This is the cleanest answer. I'll look into paramaterized queries, though it looks like this uses the OO approach to mysqli whereas I'm using procedural? – Francesca Oct 14 '14 at 08:13
It can be done oo or procedural style see [examples](http://php.net/manual/en/mysqli.prepare.php#refsect1-mysqli.prepare-examples) – andrew Oct 14 '14 at 11:23

score 0 · Answer 4 · answered Oct 13 '14 at 22:42

Solved!

It was a case of not properly wrapping the dynamic fields (the vars in the VALUES section) in ticks:

if (!mysqli_query($con,"INSERT INTO activations (userID,activationKey,isActivated) VALUES ('".$userID."','".$activateKey."','0')"))

Instead of

if (!mysqli_query($con,"INSERT INTO activations (userID,activationKey,isActivated) VALUES (".$userID.",".$activateKey.",'0')"))

Might be a difficult one to spot. The variables still need to be 'in ticks' or they won't register as strings.

score 0 · Answer 5 · edited Oct 13 '14 at 23:11

0

As activationKey is a string column, you must use single quotes for $activationKey.
Try with:

if (!mysqli_query($con,"INSERT INTO activations (userID,activationKey,isActivated)
     VALUES (".$userID.",'".$activateKey."','0')"))

edited Oct 13 '14 at 23:11

matthias_h

11,356
9
22
40

answered Oct 13 '14 at 22:43

Jorge Moreno

11
1

INSERT INTO sql query is using variable string rather than field name

5 Answers5