Escape single quote when inserting into an access database

Question

2.00/5 (3 votes)

See more:

I have an application which reads job candidates data from an Excel spreadsheeet via ODBC and populates the same in an ACCESS table via ODBC.
The problem I am facing now is that some of the text fields in Excel contain single quotes.
How can I insert such data into Access dynamically?

Thanks

What I have tried:

Visual Studio deugging Online help.

Posted 25-Jun-18 23:20pm

Member 13872723

Updated 25-Jun-18 23:56pm

Add a Solution

Comments

Patrice T 26-Jun-18 5:27am

Show your code

Richard MacCutchan 26-Jun-18 5:58am

Remove the single quotes.

3 solutions

Solution 3

Please, refer this: Error message when you use special characters in Access databases[^]

MSDN wrote:

Workaround

To work arond this problem, do not use special characters. If you must use special characters in query expressions, enclose the special characters in brackets ([]). For example, if you want to use the greater than sign (>), use [>].

BTW: i completely agree with OriginalGriff, especially about using parameterized queries[^].

Posted 25-Jun-18 23:58pm

Maciej Los

Solution 2

Replace "'" with "''".

The C++ code for such a replacement is very easy to write:

C++

void escapeTicks(std::wstring *s)
{
    for (size_t i = 0;(*s)[i] != 0;)
    {
        if ((*s)[i] == L'\'')
        {
            (*s).insert(i, L"'");
            i += 2;
        }
        else
        {
            i++;
        }
    }
}

~~I have not tested the code, but it should work~~
Actually, the old code did not work. Thanks to an anonymous commenter (the name does not appear in the comment) I have a good code.

Source: Escaping ' in Access SQL - Stack Overflow[^]

Posted 25-Jun-18 23:55pm

LLLLGGGG

Updated 27-Jun-18 7:43am

v2

Comments

Member 13872723 27-Jun-18 0:33am

sorry to say it's quite buggy.
Here's my refined version.

void escapeTicks(std::wstring *s)
{
for (size_t i = 0;(*s)[i] != 0;)
{
if ((*s)[i] == L'\'')
{
(*s).insert(i, L"'");
i += 2;
}
else
{
i++;
}
}
}

LLLLGGGG 27-Jun-18 13:39pm

Yeah, as soon as I read "it's quite buggy" I realized I wrote an horrible piece of code. Sorry for that.
Would you mind if I update my answer with your code (using the right attribution, of course)? Unfortunately, I cannot see your name on the comment.

Member 13872723 28-Jun-18 4:45am

No problem. My name is Satheesh Venkatesan

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 · 2018-06-25T23:40:00

Using parameterised queries, or via a DataTable.
The only time it gives problems is when you concatenate strings, and that's a bad idea at any time.
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?