MySQL Syntax error when inserting

Question

I've got a syntax error in the following code, but I can't find it:

$tableSelect = $_POST["tableSelect"];
$companyName = $_POST["companyName"];
$telephone = $_POST["telephone"];
$fax = $_POST["fax"];
$email = $_POST["email"];
$address = $_POST["address"];
$postcode = $_POST["postcode"];
$category = $_POST["category"];
$contact = $_POST["contact"];
$contactTel = $_POST["contactTel"];
$contactEmail = $_POST["contactEmail"];
$sql = "INSERT INTO '" . $tableSelect . "' ('" . $companyName . "', '" . $telephone . "', '"
    . $fax . "', '" . $email . "', '" . $address . "','" . $postcode . "', '" . $category . "',
    '" . $contact . "', '" . $contactTel . "', '" . $contactEmail . "')";
mysqli_query($con,$sql);
if (!mysqli_query($con,$sql)) {
    die('Error: ' . mysqli_error($con));
}

Cheers!

EDIT: I have modified the code to this:

$sql = "INSERT INTO `" . $tableSelect . "` (name, telephone, fax, email, address, postcode, category,
    contact, contactTel, contactEmail) VALUES (`" . $companyName . "`, `" . $telephone . "`, `"
    . $fax . "`, `" . $email . "`, `" . $address . "`,`" . $postcode . "`, `" . $category . "`,
    `" . $contact . "`, `" . $contactTel . "`, `" . $contactEmail . "`)";

and now have the error "Error: Unknown column [companyName] in 'field list'", where [companyName] is the value submitted through the form. But surely I've defined the column as "name"?

Edit 2: Thanks, I'm now aware of the injection issue. I'd like to get it working, then I'll change it to using prepared statements.

Aside from injection, it seems to me that you've still got no VALUES, no values at all. — Strawberry
– Strawberry, Commented Sep 6, 2013 at 11:28
You are leaving yourself wide open to SQL injection attacks. Also, any input data with single quotes in it, like a name of "O'Malley", will blow up your SQL query. Please learn about using parametrized queries, preferably with the PDO module, to protect your web app. bobby-tables.com/php has examples to get you started. — Andy Lester
– Andy Lester, Commented Sep 6, 2013 at 11:42
Don't quote/backtick your column & table names. They only add visual clutter and are just one more way for you to make syntax errors. The only reason you need them is if you have a column name that is a reserved word, and using column names that are reserved words is a terrible idea, so that's two bad habits you can avoid at once. — Andy Lester
– Andy Lester, Commented Sep 6, 2013 at 11:44
@Strawberry I've only added the code that I've changed, so the values are still there. And yes, guys, I'm aware of the injection issue now ;p I'll get it working first, then sort that out. — Barnaby
– Barnaby, Commented Sep 6, 2013 at 11:46

Gordon Linoff · Accepted Answer · 2013-09-06 11:12:09Z

2

You need either a values statement or a select statement:

"INSERT INTO '" . $tableSelect . "' VALUES ('" . $companyName . "', '" . $telephone . "', '"
. $fax . "', '" . $email . "', '" . $address . "','" . $postcode . "', '" . $category . "',
'" . $contact . "', '" . $contactTel . "', '" . $contactEmail . "')";

However, I would also recommend that you include the column names in the insert statement:

"INSERT INTO '" . $tableSelect ."(companyname, telephone, fax, email, address, postcode, category, contact, contactTel, contactEmail) ".
  "' VALUES ('" . $companyName . "', '" . $telephone . "', '"
. $fax . "', '" . $email . "', '" . $address . "','" . $postcode . "', '" . $category . "',
'" . $contact . "', '" . $contactTel . "', '" . $contactEmail . "')";

I'm not sure if those are the correct names.

answered Sep 6, 2013 at 11:12

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

1 Comment

Barnaby Over a year ago

I've included the VALUES and the column names - could you take a look at my edit?

Strawberry · Accepted Answer · 2013-09-06 12:21:51Z

1

Ignoring injection issues...

$sql = "
INSERT INTO $tableSelect 
(name
,telephone
,fax
,email
,address
,postcode
,category
,contact
,contactTel
,contactEmail
) VALUES 
('$companyName'
,'$telephone'
,'$fax'
,'$email'
,'$address'
,'$postcode'
,'$category'
,'$contact'
,'$contactTel'
,'$contactEmail'
);
";

Incidentally, in my (limited) experience, the practice of calling the variable (e.g. '$companyName') and the column (e.g. name) two (slightly) different things can get very confusing.

answered Sep 6, 2013 at 12:21

Strawberry

34k14 gold badges43 silver badges57 bronze badges

3 Comments

Barnaby Over a year ago

You sir, are a gentleman and a scholar. I shall now sort the injection issues before everyone goes apeshit on my ass ;)

Strawberry Over a year ago

You're welcome. If it's not obvious, I suppose I should point out that I have no formal qualification in MySQL (or computing generally) - oh, and my profile is supposed to be 'non-gender specific' ;-)

Barnaby Over a year ago

I do apologise ;p You, Madam, are a lady and a scholar. I'm having some trouble with the prepared-statement version though - don't suppose you could help me out on that?

Community · Accepted Answer · 2017-05-23 12:05:11Z

1

Use backquotes: ` instead of straight quotes when quoting table names:

instead of:

'" . $companyName . "'

this:

`" . $companyName . "`

Use prepared statements instead of putting the variables into the query directly. And check, that the tables names are correct, cause now you are open to SQL injection.

How can I prevent SQL injection in PHP?

edited May 23, 2017 at 12:05

CommunityBot

11 silver badge

answered Sep 6, 2013 at 11:12

user4035

24k11 gold badges69 silver badges101 bronze badges

Comments

Guru · Accepted Answer · 2013-09-06 11:15:20Z

1

please check insert query syntax

you are missing values in your program:

Follow the below Syntax:

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

answered Sep 6, 2013 at 11:15

Guru

6191 gold badge4 silver badges12 bronze badges

Comments

Kalpit · Accepted Answer · 2013-09-06 11:10:24Z

0

try query like this

$query="insert into abc (a,b,c) values ('a','b','c')

and first check your all variables using isset()

answered Sep 6, 2013 at 11:10

Kalpit

4,9365 gold badges27 silver badges45 bronze badges

Comments

Shreyas · Accepted Answer · 2013-09-06 11:16:50Z

0

Please try below query:

$sql = "INSERT INTO $tableSelect ('" . $companyName."', '".$telephone."',
'".$fax."', '".$email."', '".$address."', '".$postcode."', '".$category."',
'".$contact."', '".$contactTel."', '".$contactEmail."')";

If still getting error, then you should use mysql_real_escape_string() function.
Data may contain special characters.

answered Sep 6, 2013 at 11:16

Shreyas

2571 silver badge14 bronze badges

Collectives™ on Stack Overflow

MySQL Syntax error when inserting

6 Answers 6

1 Comment

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related