An unhandled exception of type 'system.data.oledb.oledbexception' occurred in system.data.dll additional information: syntax error in INSERT into statement.

Question

1.00/5 (1 vote)

See more:

this is where my problem take place,the Data Base class:

C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.OleDb;
using System.Data;

namespace LibraryProject
{
    class DataBase
    {
        public static void Execute(String SQL)
        {
            OleDbConnection cn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=LibraryProject.mdb");
            cn.Open();
            OleDbCommand cm = new OleDbCommand();
            cm.ExecuteNonQuery();
            cn.Close();
        }

        public static DataTable ExecuteSelect(String SQL)
        {
            OleDbConnection cn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=LibraryProject.mdb");
            cn.Open();
            OleDbDataAdapter da = new OleDbDataAdapter(SQL,cn);
            DataTable dt = new DataTable();
            da.Fill(dt);
                cn.Close();
            return dt;
        }
    }
}

the problem's place is in front of this line==>cn.close();

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
Additional information: Syntax error in INSERT INTO statement.

this is where I wrote Insert function,class userpass:

C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;

namespace LibraryProject
{
    class userpass
    {
        public String user { get; set; }
        public String pass { get; set; }




        public userpass(String user , String pass)
        {
            this.user = user;
            this.pass = pass;
        }
        public userpass()
        {
            user = "-";
            pass = "-";
        }
        

      public void Insert()
        {
            String SQL;
            SQL = "INSERT INTO userpass(user,pass) VALUES ('" + this.user + "','" + this.pass + "')";
            DataBase.ExecuteSelect(SQL);
        }

    

        public Boolean CheckUserPass(String u, String p)
        {
            String SQL;
            SQL = "SELECT * FROM userpass WHERE user='"+u+"' AND pass='"+p+"' ";
            DataTable dt = new DataTable();
            dt = DataBase.ExecuteSelect(SQL);
            if (dt.Rows.Count == 0)
                return false;
            else
                return true; 
        }

    }
}

and finally how I recall Insert in SAVE button in my form:

C#

userpass ms = new userpass();
            ms.user = txtuser.text;
            ms.pass = txtpass.Text;
            ms.Insert();

What I have tried:

Sorry but as I said before I have tried any way around the problem cause our mentor told us our way is TRUE and SHOULD work properly so I don't know what to do now!!!

Posted 23-Feb-19 22:24pm

User 14118045

Updated 24-Feb-19 7:05am

Blutfaust

v2

Add a Solution

3 solutions

Solution 2

Please read Solution 1 carefully.

Now to fix your dangerous code. I see two things:

1.) public void Insert()

C#

public void Insert()
{
String SQL;
SQL = "INSERT INTO userpass(user,pass) VALUES ('" + this.user + "','" + this.pass + "')";

// **** The following should be DataBase.Execute(SQL) **** 
DataBase.ExecuteSelect(SQL);
}

2.) public static void Execute(String SQL)
This is not yet finished. You neither give the OleDbCommand the SQL nor the connection.

Finally take care for resources. It means for classes which implement IDisposable it makes sense to get the help of using. For more details read e.g. this: c# - When Using statement should be used? - Stack Overflow[^]

Posted 23-Feb-19 23:50pm

User 11060979

Updated 24-Feb-19 0:03am

v2

Comments

[no name] 27-Feb-19 4:11am

thank you for your useful answer,but there's something else;I use the same wrong code for my project above(same DataBase,same Insert function)for a form named addnewbook with 4 textbox and it did work and still works but when I add another Insert function (as you see it above,the userpass Insert) or add another textbox inside add newbook
with all it's geter and seter and ... ) It responds me with that cursed syntax error.
I will be appreciate if you take look at this and give me your idea.
thank you.

[no name] 27-Feb-19 4:18am

Did you meanwhile replace DataBase.ExecuteSelect(SQL); by DataBase.Execute(SQL); and implemented public static void Execute(String SQL)?

If yes, what is your actual code?

Solution 3

Quote:
Sorry but as I said before I have tried any way around the problem cause our mentor told us our way is TRUE and SHOULD work properly

Teach your mentor about 'SQL Injection'.

Not necessary a solution to your question, but another problem you have.
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
SQL Injection Prevention Cheat Sheet - OWASP[^]
How can I explain SQL injection without technical jargon? - Information Security Stack Exchange[^]

Posted 23-Feb-19 23:56pm

Patrice T

Comments

[no name] 27-Feb-19 4:11am

thank you for your useful answer,but there's something else;I use the same wrong code for my project above(same DataBase,same Insert function)for a form named addnewbook with 4 textbox and it did work and still works but when I add another Insert function (as you see it above,the userpass Insert) or add another textbox inside add newbook
with all it's geter and seter and ... ) It responds me with that cursed syntax error.
I will be appreciate if you take look at this and give me your idea.
thank you.

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)

OriginalGriff · Accepted Answer · 2019-02-23T22:50:00

I don't care what your mentor says: that's dangerous.
Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

Which SQL sees as three separate commands:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';

A perfectly valid SELECT

SQL

DROP TABLE MyTable;

A perfectly valid "delete the table" command

SQL

--'

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

C#

using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlCommand cmd = new SqlCommand("INSERT INTO myTable (myColumn1, myColumn2) VALUES (@C1, @C2)", con))
        {
        cmd.Parameters.AddWithValue("@C1", myValueForColumn1);
        cmd.Parameters.AddWithValue("@C2", myValueForColumn2);
        cmd.ExecuteNonQuery();
        }
    }

Fix that throughout your app, and your problem may go away.

In addition, you should never store passwords in clear text - it is a major security risk. There is some information on how to do it here: Password Storage: How to do it.[^]