C# to SQLite Email String

Question

Trying to store an email address from a .NET application into a SQLite database. The DB is pre-built within the SQLite studio and I have already connected my project to it.

Problem is, when I attempt to pass an email address (String), into the DB with the following:

Account.Email = test@test.com
string SQLText = "INSERT " + "INTO Account (Email) " + "VALUES (" + Account.Email + ")"
SqliteCommand Command = new SQLiteCommand (SQLText, Base);
Command.ExecuteNonQuery ();

Account being a class that holds the email string and Base being a reference to the DB with C#.

I get

SQLiteSyntaxException: near "@Test": syntax error

What should I do to the string in order for it to ignore the @ as a parameter declaration in sql?

Any advice?

Sean

Just use parameters and your problem will be gone ? Its safer anyway. A example how to do this is here: http://stackoverflow.com/a/809275/2354446 — DatRid, Jun 03 '15 at 13:30
Quote the string: `"VALUES ('" + Account.Email + "')"` but [**use a parametrized insert**](http://stackoverflow.com/questions/809246/adding-parameters-in-sqlite-with-c-sharp) instead; you have a SQL injection vulnerability — Alex K., Jun 03 '15 at 13:32
What is the type of `Email` column and what is the type and value of `Account.Email` ? — Soner Gönül, Jun 03 '15 at 13:32
What happend if you print out your SQL statement and try to execute it in SQL lite directly? — Reporter, Jun 03 '15 at 13:33
@ DatRid: I am so-so with .Net and brand new at SQL so I will have to do some research on parameter use in SQL. Thanks for the resonse — Sean Barnard, Jun 03 '15 at 13:33
@SeanBarnard That's just normal, but its better if you learn it the right way so that you get used to it - that's why I commented :-) — DatRid, Jun 03 '15 at 13:34
@ Soner: Email = STRING in SQL and is Primary Key and Unique, Email is a string value in C# and in this example is "test@test.com". I hope thta answered your question. Thanks — Sean Barnard, Jun 03 '15 at 13:36
@ reporter: I have ran the simple insert in the studio. it worked if i explicitly quoted the entire email address. INSERT into Account (Email) VALUES ("test@test.com") — Sean Barnard, Jun 03 '15 at 13:38
@SeanBarnard Then have a look at comment from Aex K. and the existing answer — Reporter, Jun 03 '15 at 13:56

score 0 · Accepted Answer · answered Jun 03 '15 at 13:33

0

I don't know about sqlite specifically, but with my knowledge of SQL syntax in general, the resulting statement should look like (assuming the email address is email@test.com):

INSERT INTO Account(Email) Values ('email@test.com')

But your code produces the following statement:

INSERT INTO Account(Email) Values (email@test.com)

You'll notice the single quotes are missing from the second line.

Also, you might want to look into what a SQL injection attack is. Building your queries by concatenating strings with user input opens you up to this type of attack.

answered Jun 03 '15 at 13:33

Brian Ball

12,268
3
40
51

Technically since my account class holds the value as a string I would assume the quotes are assumed since I am passing it in directly from the class. Account.Email SHOULD = "email@test.com". But maybe the quotes are lost in translation. I am looking into "parameterizing" my commands apparently this is safer. Thanks for the answer. – Sean Barnard Jun 03 '15 at 13:57
It doesn't work like that. Take this code snip-it: `string email = "email@test.com";` The quotes aren't part of the string value, the quotes simply tell the compiler that the characters between the quotes represent a string value, the actual value in the email variable is `email@test.com`. Parameterizing your query is definitely the right way to go. Another added bonus is that you won't have to worry about quotes anymore. – Brian Ball Jun 03 '15 at 14:37

C# to SQLite Email String

1 Answers1