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, Sep 06 '13 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. http://bobby-tables.com/php has examples to get you started. — Andy Lester, Sep 06 '13 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, Sep 06 '13 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, Sep 06 '13 at 11:46

score 2 · Answer 1 · answered Sep 06 '13 at 11:12

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.

I've included the VALUES and the column names - could you take a look at my edit? — Barnaby, Sep 06 '13 at 11:42

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

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 '17 at 12:05

Community

1
1

answered Sep 06 '13 at 11:12

user4035

22,508
11
59
94

score 1 · Answer 3 · answered Sep 06 '13 at 11:15

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 06 '13 at 11:15

Guru

621
1
4
12

score 1 · Accepted Answer · answered Sep 06 '13 at 12:21

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 06 '13 at 12:21

Strawberry

33,750
13
40
57

You sir, are a gentleman and a scholar. I shall now sort the injection issues before everyone goes apeshit on my ass ;) – Barnaby Sep 06 '13 at 12:26
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' ;-) – Strawberry Sep 06 '13 at 12:30
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? – Barnaby Sep 06 '13 at 12:45

score 0 · Answer 5 · answered Sep 06 '13 at 11:10

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 06 '13 at 11:10

Kalpit

4,906
4
25
43

score 0 · Answer 6 · answered Sep 06 '13 at 11:16

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.

MySQL Syntax error when inserting

6 Answers6