Error with C# mySQL INSERT statement - "You have an error in your SQL syntax"

Question

I keep getting the same vague error with this code:

command.CommandText = "INSERT INTO database (upc, title, description, quantity) VALUES ('"+ upc.Text +"',"+"'"+titlename+"',"+ "'"+descname+"',"+ "'1'"+"), MyConString";

The error is:

{"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 'database (upc, title, description, quantity) VALUES ('016000165779','Betty Crock' at line 1"}

I'm new at C# and trying to build a program that uses UPC codes that get inserted into a mySQL database.

One of the biggest things that's wrong with it is the SQL injection hole — Josh Darnell
– Josh Darnell, Commented Jan 30, 2012 at 17:26
@jadarnel27, agreed! At this point in history there is virtually no reason left to be making queries out of string concatenations. Just learning how to code? Please... don't learn how to code with SQL injections baked right in! — Godeke
– Godeke, Commented Jan 30, 2012 at 17:28
no i don't have the quotes actually being inserted as data. i read examples of the statement and thats how they wrote it — Devin Prejean
– Devin Prejean, Commented Jan 30, 2012 at 17:29
@DevinPrejean: unfortunately the internet is littered with code samples that wouldn't pass any sort of basic security audit. Usually written by those that really aren't sure of what they are doing. — ChrisLively
– ChrisLively, Commented Jan 30, 2012 at 17:30

Josh Darnell · Accepted Answer · 2012-01-30 17:42:39Z

4

Try this:

command.CommandText = "INSERT INTO tableName " +
                          "(upc, title, description, quantity) " +
                      "VALUES " +
                          "(@upc, @title, @description, @quantity)";

command.Parameters.AddWithValue("@upc", upc.Text);
command.Parameters.AddWithValue("@title", titlename);
command.Parameters.AddWithValue("@description", descname);
command.Parameters.AddWithValue("@quantity", "1");

Notes:

This fixes your SQL injection hole by using parametrized queries. Especially where I see upc.Text, that makes me think that your concatenating user input into your SQL string (very dangerous).
I changed the word "database" to "tableName" in your query. That is where the name of a table goes, not the database name.
I neatened up your string declaration a little, so that it is easier to read =)

edited Jan 30, 2012 at 17:42

answered Jan 30, 2012 at 17:36

Josh Darnell

11.5k9 gold badges40 silver badges66 bronze badges

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

8 Comments

Devin Prejean Over a year ago

the name of the table IS database... should i change that to something else i just put a name out there

Josh Darnell Over a year ago

@DevinPrejean You definitely want to name the table something more descriptive, like upcCodes or productUPCs. Something that describes what is in the table. As DorSherner mentioned in his answer, you can put backticks around the table name if you need it to stay "database".

Devin Prejean Over a year ago

oh i changed it and it throws a new error but i think this will be easier. it says the column UPC is out of range. its likely because the number is too big. i set the max to INT(45) on the column but that didn't work.

ChrisLively Over a year ago

@DevinPrejean: the table name should always describe the contents.

ChrisLively Over a year ago

@DevinPrejean: Also don't store UPC codes as numbers... ;) Just use a char() or varchar() field. You aren't going to be adding them together. Also, leading zeros can be significant depending on the system.

|

dorsh · Accepted Answer · 2012-01-30 17:27:28Z

3

database might be a reserved word. Try escaping it with backticks:

INSERT INTO `database` ...

I'm also not sure why the , MyConString part is inside the query itself, but I'm no C# expert.

answered Jan 30, 2012 at 17:27

dorsh

24.9k2 gold badges30 silver badges30 bronze badges

2 Comments

dorsh Over a year ago

@a1ex07 I get that way too much :(

Devin Prejean Over a year ago

well that did change the error finally... but its picking up part of the "description" of the product and throws an error at a certain point. the new error code: {"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 's a snap. US inspected and passed by Department of Agriculture.','1'), MyConStri' at line 1"}

ChrisLively · Accepted Answer · 2012-01-30 17:27:31Z

3

Why do you have ", MyConString" at the end of your query? That seems odd.

Also, database is probably not the name of your table.

answered Jan 30, 2012 at 17:27

ChrisLively

88.3k27 gold badges174 silver badges249 bronze badges

1 Comment

Devin Prejean Over a year ago

database is the name of the table, and myconstring is at the end because that identifies the connection. its all in my code i didn't show.

Ravi Gadag · Accepted Answer · 2012-01-30 17:37:36Z

1

Database is keyword in mysql, try to give descriptive names, so that it will help to understand. and use Parameterized query to avoid sql injectionsSqlInjections.

This is the mysql keyword list : MySql Reserved keywords, in future try to avoid using keywords.

    command.CommandText = "INSERT INTO [database] (upc, title, description, quantity) VALUES (@upc,@title ... ) 
command.Parameters.AddWithValue("@upc","upcValue");
command.Parameters.AddWithValue("@title","titleValue");

answered Jan 30, 2012 at 17:37

Ravi Gadag

15.9k5 gold badges60 silver badges85 bronze badges

Comments

Diego · Accepted Answer · 2012-01-30 17:28:13Z

0

I think you are missing a double quote in the end before the coma:

"+")", MyConString";

answered Jan 30, 2012 at 17:28

Diego

36.5k21 gold badges94 silver badges138 bronze badges

1 Comment

Devin Prejean Over a year ago

nope. the black is not being picked up. the red colored text is what the program reads

Collectives™ on Stack Overflow

Error with C# mySQL INSERT statement - "You have an error in your SQL syntax"

5 Answers 5

8 Comments

2 Comments

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

8 Comments

2 Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related