Syntax error in INSERT into statement

Question

1.00/5 (1 vote)

See more:

Hi, let me start with the fact that I have a SYNTAX ERROR INSERT into statement I use visual studio ultimate 2013 and ACCESS 2016 also I use C# for codings.
I've tried every way possible to fix this problem but i'm a kinda rookie in c# and using Visual Studio and I really run outta ideas, please help me,thank you!
here is my problem, in front of the 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.

What I have tried:

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;
        }
    }
}

Posted 23-Feb-19 3:48am

User 14118045

Updated 25-Feb-19 7:22am

RickZeeland

v2

Add a Solution

Comments

Richard Deeming 25-Feb-19 14:43pm

The first step to fixing the SQL Injection[^] vulnerabilities in your code is to fix your class so that you can pass parameters properly.

static class DataBase
{
    public static void Execute(String commandText, params OleDbParameter[] commandParameters)
    {
        if (string.IsNullOrWhiteSpace(commandText)) throw new ArgumentNullException(nameof(commandText));
        if (commandParameters == null || commandParameters.Length == 0) throw new InvalidOperationException("Possible SQL Injection vulnerability detected.");
        
        using (OleDbConnection cn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=LibraryProject.mdb"))
        using (OleDbCommand cm = new OleDbCommand(commandText, cn))
        {
            cm.Parameters.AddRange(commandParameters);
            
            cn.Open();
            cm.ExecuteNonQuery();
        }
    }

    public static DataTable ExecuteSelect(String commandText, params OleDbParameter[] commandParameters)
    {
        if (string.IsNullOrWhiteSpace(commandText)) throw new ArgumentNullException(nameof(commandText));
        if (commandParameters == null || commandParameters.Length == 0) throw new InvalidOperationException("Possible SQL Injection vulnerability detected.");
        
        using (OleDbConnection cn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=LibraryProject.mdb"))
        using (OleDbCommand cm = new OleDbCommand(commandText, cn))
        {
            cm.Parameters.AddRange(commandParameters);
            
            OleDbDataAdapter da = new OleDbDataAdapter(cm);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
    }
}

The code will now throw an InvalidOperationException if you try to execute a query without providing any parameters, alerting you to the fact that you need to fix the SQL Injection vulnerability.

4 solutions

Add a Solution

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

Patrice T · Answer 1 · 2019-02-25T07:22:00

Quote:
Syntax error in INSERT into statement

When asking for help, the first step is showing the offending code, the 'INSERT' statement.

The most probable reason of the problem is that your code is sensible to SQL Injection.

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[^]

OriginalGriff · Answer 2 · 2019-02-23T04:28:00

Look at the error message:

Syntax error in INSERT INTO statement.

So start by looking at exactly what is in SQL (the variable) when you execute that code: it's wrong, and only you can tell exactly what is in it by using the debugger to find out while your code is running.

We can't even see where you call that code from, much less what you pass into those methods.

Maciej Los · Answer 3 · 2019-02-24T21:39:00

An error message is self-explanatory. Your insert command is wrong! It's not a matter of Visual Studio.

A proper INSERT statement you'll find here: INSERT INTO statement (Microsoft Access SQL) | Microsoft Docs[^]

Note: for MS Access 2016 database, i'd use Microsoft Access Database Engine 2016 Redistributable[^] with ACE.OLEDB provider.
For further details, please see: Access connection strings - ConnectionStrings.com[^]

RmcbainTheThird · Answer 4 · 2019-02-25T07:15:00

Solution 3

Copy your insert statement to the clipboard, open up access, paste in the query and see what happens

Posted 25-Feb-19 7:15am

RmcbainTheThird

Syntax error in INSERT into statement

4 solutions

Solution 4

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Syntax error in INSERT into statement

4 solutions

Solution 4

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us