sql syntax error when creating column

Question

I am trying to run the following query, but am getting a sql syntax error, but dreamweaver doesnt seem to be highlighting any error so im not sure where it is ?. thanks :-)

<?php
$form_id = $_POST[form_id];
    $query = mysql_query("
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE 
TABLE_NAME = 'email_history' 
AND COLUMN_NAME = `$form_id`)
BEGIN
 ALTER TABLE 'email_history' ADD `$form_id` VARCHAR( 255 ) NOT NULL
END;
") or die(mysql_error());
?>

after changing the ' to a ` I am still getting a syntax error on line 1 — Iain Simpson, Jan 01 '12 at 20:25
What is the SQL syntax error message? dreamweaver doesn't analyze SQL for you. It just sees you passing a string to a function call. — Marc B, Jan 01 '12 at 20:29
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_N' at line 1 — Iain Simpson, Jan 01 '12 at 20:34

KingCrunch · Answer 1 · 2012-01-01T20:55:31.783

1

Have a look at

‘$form_id`

You probably mean

`$form_id`

Update: Once more wrong quotes

'email_history'

should be

`email_history`

Remind: Backticks ` are for qualifiers (tablenames and such), where the apostroph ' is for values.

edited Jan 01 '12 at 20:55

answered Jan 01 '12 at 20:23

KingCrunch

128,817
21
151
173

Hi, I have changed that , but am still getting a sql syntax error on line 1 – Iain Simpson Jan 01 '12 at 20:27
Updated. Just take a little bit more care about what is a value and what is an identifier. – KingCrunch Jan 01 '12 at 20:56

Shiplu Mokaddim · Answer 2 · 2012-01-01T20:58:56.720

1

Here is the correct quotation format.

$query = mysql_query("
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE 
TABLE_NAME = 'email_history' 
AND COLUMN_NAME = '$form_id')
BEGIN
    ALTER TABLE `email_history` ADD `$form_id` VARCHAR( 255 ) NOT NULL
END;
") or die(mysql_error());

edited Jan 01 '12 at 20:58

answered Jan 01 '12 at 20:35

Shiplu Mokaddim

56,364
17
141
187

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'emai' at line 1 – Iain Simpson Jan 01 '12 at 20:39
I dont get an error with the above query, but it also doesn't create a column in email history either, even if I clear all the columns from the email_history table. – Iain Simpson Jan 01 '12 at 20:51

score 0 · Answer 3 · answered Jan 01 '12 at 21:01

0

Try this.

$form_id = $_POST[form_id];
$query = mysql_query("
  IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
    WHERE 
    TABLE_NAME = 'email_history' 
    AND COLUMN_NAME = '$form_id')
  BEGIN
    ALTER TABLE `email_history` ADD `$form_id` VARCHAR( 255 ) NOT NULL
  END;
") or die(mysql_error());

Also, just on side note, you should never directly use the values from User input into your SQL. This is to prevent SQL injection. Here are a few links on MySQL Injection which I found:

http://www.php.net/manual/en/security.database.sql-injection.php http://25yearsofprogramming.com/blog/2011/20110205.htm https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet

answered Jan 01 '12 at 21:01

Virendra

2,560
3
23
37

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_N' at line 1 – Iain Simpson Jan 01 '12 at 21:07
I just updated my answer. Did you try this one or old one? The difference is in `AND COLUMN_NAME = '$form_id')` and ALTER TABLE `email_history` – Virendra Jan 01 '12 at 21:09
I get this error, with the current version you have pasted >> – Iain Simpson Jan 01 '12 at 21:14
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_N' at line 1 – Iain Simpson Jan 01 '12 at 21:14
A solution would be to just try alter table and handle the error if the column already exists. You would get error similar to this if the column already exists. `Duplicate column name 'newcolumnname'`. You would catch it. – Virendra Jan 01 '12 at 21:19
how can I dismiss the error, as this solution would be ok if I could get rid of the error. – Iain Simpson Jan 01 '12 at 21:21
I tried this, but when you edit the form it then creates a new column for the form – Iain Simpson Jan 01 '12 at 21:28
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/6296/discussion-between-virendra-and-iain-simpson) – Virendra Jan 01 '12 at 21:30

score 0 · Answer 4 · answered Jan 01 '12 at 21:04

0

<?php
$form_id = $_POST[form_id];
    $query = mysql_query("
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE 
TABLE_NAME = 'email_history' 
AND COLUMN_NAME = '$form_id')
BEGIN
 ALTER TABLE email_history ADD `$form_id` VARCHAR( 255 ) NOT NULL
END;
") or die(mysql_error());
?>

answered Jan 01 '12 at 21:04

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'e' at line 1 – Iain Simpson Jan 01 '12 at 21:16
1

Just found this - http://stackoverflow.com/questions/972922/add-column-to-mysql-table-if-it-does-not-exist – Jan 01 '12 at 21:20

score 0 · Accepted Answer · answered Jan 01 '12 at 21:27

Everyone has focused on bad quoting, but is there any chance of this query to run? Does MySQL allow such conditional statements? I know this is possible in stored routines but I have never heard of such direct usage, so I would split this into two queries: column existence check and table alteration.

This will look like this:

<?php
$form_id = some_function_to_check_if_input_is_valid_etc($_POST[form_id]);
$exist_result = mysql_query("SELECT IF(NOT EXISTS(
    SELECT * 
    FROM    INFORMATION_SCHEMA.COLUMNS
    WHERE   TABLE_NAME  = 'email_history' 
    AND     COLUMN_NAME = '{$form_id}'
    ),1,0) AS should_alter_table
");
$row = mysql_fetch_assoc($exist_result);
if($row['should_alter_table']){
    mysql_query("ALTER TABLE email_history ADD `{$form_id}` VARCHAR( 255 ) NOT NULL");
}

sql syntax error when creating column

5 Answers5