Click here to Skip to main content
14,330,218 members
Rate this:
Please Sign up or sign in to vote.
Word containing single quote breaks to next column after single quote while filling to OleDb Dataset in csv file import
Word - 4' 9 test June 2019

What I have tried:

string sql = @"SELECT * FROM [" + this.FileName + "]";
            using (OleDbConnection objConnection = new OleDbConnection(ConnectionString))
            using (OleDbCommand command = new OleDbCommand(sql, objConnection))
            using (OleDbDataAdapter objDataAdapter = new OleDbDataAdapter(command))
            {

                try
                {
                    objConnection.Open();
                    objDataAdapter.Fill(dsData);
                }
                catch (Exception ex)
                {}
}
Posted
Updated 3-Sep-19 14:29pm
v2
Comments
CHill60 3-Sep-19 7:57am
   
Your code is vulnerable to SQL Injection attacks.
You are "swallowing" any exceptions - get rid of the try-catch or at very least alert the user to any errors
Member 11220730 3-Sep-19 7:58am
   
My issue is Word containing single quote breaks to next column after single quote while filling to oledb dataset in csv file import
Richard MacCutchan 3-Sep-19 8:20am
   
What word, where, which line of code? Please edit your question and provide proper details of your problem.
Patrice T 3-Sep-19 8:26am
   
Show sample record that make error.
Rate this:
Please Sign up or sign in to vote.

Solution 3

Quote:
Word containing single quote breaks to next column after single quote while filling to OleDb Dataset in csv file import

When I import a csv (like) file in Excel, I am asked what is the field separator and what is the string delimiter.
Where do you tell those 2 informations in this code.
My experience is that I get least problems with tabs as field separator and nothing for strings delimiter.

You still haven't show sample data.
   
Rate this:
Please Sign up or sign in to vote.

Solution 2

Quote:
Word containing single quote breaks to next column after single quote while filling to OleDb Dataset in csv file import
The reason is—as explained in Solution 1—that your input is not sanitized. Your query is like this,
SELECT * FROM ['Employee];
INSERT INTO ...
As you can see, your SQL command when built using string concatenation, leaves the string values unescaped and string (as in your case) when not properly quoted can flow over to the next lines.

The solution to this is to use parameters as input, which can help your SQL query builder to properly sanitize the inputs and escape any unwanted values. This can lead to prevention of SQL Injection and these kind of non-SQL Injection problematic inputs. It is possible that a user might input the values unintentionally. Read this fun comic for a humor, xkcd: Exploits of a Mom[^].

Check out this documentation page on Microsoft Docs to learn how this can work in a real world solution, OleDbCommand.Parameters Property (System.Data.OleDb) | Microsoft Docs[^]
   
Rate this:
Please Sign up or sign in to vote.

Solution 1

Simple solution: 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:
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:
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
DROP TABLE MyTable;
A perfectly valid "delete the table" command
--'
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?

And your problem will go away at the same time...
   

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100