C# SQL syntax error when trying to insert data with Microsoft access .mdb

Question

1.00/5 (1 vote)

See more:

static OleDbConnection conn = new OleDbConnection(ConfigurationManager.ConnectionStrings["dbProject"].ConnectionString);

Im getting SQL syntax error when executing the query.

C#

public static void InsertLogins(User u)
      {
          conn.Open();
          OleDbCommand cmd = new OleDbCommand("Insert into tblLogin(Username,Password,CharPreset) values ('" + u.Username + "','" + u.Password + "','" + u.CharPreset + "')", conn);

          cmd.ExecuteNonQuery();

          conn.Close();

      }

I can read from the database using this query

C#

OleDbCommand cmd = new OleDbCommand("Select * From tblLogin", conn);

What I have tried:

Everything I could think of. It works when using SQL server but I want it to work with Microsoft Access

Posted 17-May-18 8:42am

Member 12462239

Updated 17-May-18 9:36am

v2

Add a Solution

Comments

Richard MacCutchan 17-May-18 15:09pm

And please don't tell us you are storing passwords in clear text.

[no name] 17-May-18 15:11pm

Just because a "select" works, doesn't mean an "insert" will.

"Hard code" your insert until it works; THEN "parameterize" it.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Answer 1 · 2018-05-17T08:47:00

Solution 1

First, Google for "SQL Injection Attack" to find out why what you're doing is so bad.

Next, Google for "C# sql parameterized queries" to find out how to solve that problem and the problem you're running into.

Posted 17-May-18 8:47am

Dave Kreskowiak

Richard Deeming · Answer 2 · 2018-05-17T09:37:00

Some light reading for you:
Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

And:
Secure Password Authentication Explained Simply[^]
Salted Password Hashing - Doing it Right[^]

An immediate fix for the SQLi vulnerability:

using (OleDbCommand cmd = new OleDbCommand("INSERT INTO tblLogin (Username, Password, CharPreset) VALUES (?, ?, ?)", conn))
{
    cmd.Parameters.AddWithValue("p0", u.Username);
    cmd.Parameters.AddWithValue("p1", u.Password);
    cmd.Parameters.AddWithValue("p2", u.CharPreset);
    
    cmd.ExecuteNonQuery();
}

But don't ignore the password storage problem!

Oh, and storing the connection object in a static field is a terrible idea, especially if this is a web application. Connections are not thread-safe, and if two requests hit at the same time, the best you can hope for is a crash.