Syntax error in INSERT INTO statement with ASP.NET

Question

Here is the query:

INSERT INTO `users` (username, password, email) VALUES ('testu', 'testp', 'teste')

I am getting this error with the following VB code (ASP):

    username = Request.Form("username")
    password = Request.Form("password")
    email = Request.Form("email")

    Dim conn As OleDbConnection = New OleDbConnection(GetConnectionString())
    Dim comm As OleDbCommand = New OleDbCommand("INSERT INTO users (username, password, email) VALUES ('" & username & "', '" & password & "', '" & email & "')", conn)

    conn.Open()
    comm.ExecuteNonQuery()

What is wrong with this statement? I know the connection string is correct because I have used it throughout the rest of my application.

FYI... your code is open to sql inject... and what is the exact error message your get? — John Sobolewski, Dec 06 '11 at 12:30
It is for a very small school project, I am not worried about SQL injection. — Logan Serman, Dec 06 '11 at 12:34
Try this: `Dim query as String = "INSERT INTO users (...` copying your query. Then edit your question posting `query` value. As someone said, there could be some _strange_ char in params... — Marco, Dec 06 '11 at 12:38
No, it's not: your query seems like `"INSERT INTO users` while your second line uses table name within backticks! — Marco, Dec 06 '11 at 12:41
I changed the backticks to see if that made a difference while typing the question. What I posted is what I get when I output the query. — Logan Serman, Dec 06 '11 at 12:43
And just to be clear: you **must** always be worried about SQL injection and you should never store passwords in plain text. It's a really bad practice! — Marco, Dec 06 '11 at 12:43
Marco, I am well aware of those problems which I have said several times. Thanks though, I guess. — Logan Serman, Dec 06 '11 at 12:46

score 2 · Accepted Answer · edited May 23 '17 at 10:08

2

Agree! Always use Parameter (prepared) query and hash salt your password. Read SO thread - Salting Your Password: Best Practices? .

 Dim conn As OleDbConnection = New OleDbConnection(GetConnectionString())
 Dim comm As OleDbCommand = New OleDbCommand("INSERT INTO users (username, [password], email) VALUES (@username,@password,@email)",conn)
 comm.Parameters.Add("@username",OleDbType.Varchar,30).Value=username
 ....

edited May 23 '17 at 10:08

Community

1
1

answered Dec 06 '11 at 12:35

kv-prajapati

90,019
18
141
178

Like I said it is for a very small school project. I am aware of prepared queries and salting. The problem here is the query I mentioned not working, and I do not believe using parameters will solve that (but I could be wrong). – Logan Serman Dec 06 '11 at 12:37
@LoganSerman - Password is reserved word of access database engine so you have to escape. See the edited post. http://office.microsoft.com/en-us/access-help/access-2007-reserved-words-and-symbols-HA010030643.aspx – kv-prajapati Dec 06 '11 at 12:47
Thank you, AVD. That was indeed the problem. – Logan Serman Dec 06 '11 at 12:48

score 1 · Answer 2 · answered Dec 06 '11 at 12:30

Don't EVER pass parameters to SQL commands with concatenation! This is an open way for SQL injection attack.

You should use placeholders for parameters and add actual values using comm.AddParameter().

As for your error - I'd expect some of your parameter values contains a single quote (').

Syntax error in INSERT INTO statement with ASP.NET

2 Answers2