escape the ' and " chars to insert them onto an Access database

[Luminal]

Greetings

I'm having some problems on my C# application. I'm using an access
database and I'm not able to do select queries with the ' character.

My code is this:

// some previous code like open connection

string sqlStatement="S ELECT oid FROM formatos WHERE formato='cd\\'s '";

myComm.CommandT ext=sqlStatemen t;
OleDbDataReader myDR=myComm.Exe cuteReader();

//... and so on

I get the exception message

AccessException : Syntax error (missing operator) in query expression
'formato='cd\'s ''.
DeclaringType.F ullName:: System.Data.Ole Db.OleDbCommand
Method - Void ExecuteCommandT extErrorHandlin g(Int32)
at
System.Data.Ole Db.OleDbCommand .ExecuteCommand TextErrorHandli ng(Int32 hr)
at
System.Data.Ole Db.OleDbCommand .ExecuteCommand TextForSingleRe sult(tagDBPARAM S
dbParams, Object& executeResult)
at System.Data.Ole Db.OleDbCommand .ExecuteCommand Text(Object&
executeResult)
at System.Data.Ole Db.OleDbCommand .ExecuteCommand (CommandBehavio r
behavior, Object& executeResult)
at
System.Data.Ole Db.OleDbCommand .ExecuteReaderI nternal(Command Behavior
behavior, String method)
at System.Data.Ole Db.OleDbCommand .ExecuteReader( CommandBehavior
behavior)
at System.Data.Ole Db.OleDbCommand .ExecuteReader( )
at
....


AccessError: Syntax error (missing operator) in query expression
'formato='cd\'s ''.
Source: Microsoft JET Database Engine
Native Error: -524553244
State: 3075

How can I escape the ' and " chars so I can insert them onto an Access
database?

TIA

Lum

[Mythran]

Re: escape the ' and " chars to insert them onto an Access database

With access use the following string:

string sqlStatement = "SELECT oid FROM formatos WHERE formato = \"cd's\"";

Try that :)

Mythran


"Luminal" <luminal_NOSPAM @hotpop_NOSPAM. com> wrote in message
news:eKDKJHwpEH A.324@TK2MSFTNG P11.phx.gbl...[color=blue]
> Greetings
>
> I'm having some problems on my C# application. I'm using an access
> database and I'm not able to do select queries with the ' character.
>
> My code is this:
>
> // some previous code like open connection
>
> string sqlStatement="S ELECT oid FROM formatos WHERE formato='cd\\'s '";
>
> myComm.CommandT ext=sqlStatemen t;
> OleDbDataReader myDR=myComm.Exe cuteReader();
>
> //... and so on
>
> I get the exception message
>
> AccessException : Syntax error (missing operator) in query expression
> 'formato='cd\'s ''.
> DeclaringType.F ullName:: System.Data.Ole Db.OleDbCommand
> Method - Void ExecuteCommandT extErrorHandlin g(Int32)
> at
> System.Data.Ole Db.OleDbCommand .ExecuteCommand TextErrorHandli ng(Int32 hr)
> at
> System.Data.Ole Db.OleDbCommand .ExecuteCommand TextForSingleRe sult(tagDBPARAM S
> dbParams, Object& executeResult)
> at System.Data.Ole Db.OleDbCommand .ExecuteCommand Text(Object&
> executeResult)
> at System.Data.Ole Db.OleDbCommand .ExecuteCommand (CommandBehavio r
> behavior, Object& executeResult)
> at
> System.Data.Ole Db.OleDbCommand .ExecuteReaderI nternal(Command Behavior
> behavior, String method)
> at System.Data.Ole Db.OleDbCommand .ExecuteReader( CommandBehavior
> behavior)
> at System.Data.Ole Db.OleDbCommand .ExecuteReader( )
> at
> ...
>
>
> AccessError: Syntax error (missing operator) in query expression
> 'formato='cd\'s ''.
> Source: Microsoft JET Database Engine
> Native Error: -524553244
> State: 3075
>
> How can I escape the ' and " chars so I can insert them onto an Access
> database?
>
> TIA
>
> Lum[/color]

[Nicholas Paldino [.NET/C# MVP]]

Re: escape the ' and &quot; chars to insert them onto an Access database

Luminal,

Instead of trying to figure out the quotes yourself, I would recommend
using a parameterized query, and then just set the value of the parameter to
the value you have. The provider will take care of formatting the values
correctly.

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- mvp@spam.guard. caspershouse.co m

"Luminal" <luminal_NOSPAM @hotpop_NOSPAM. com> wrote in message
news:eKDKJHwpEH A.324@TK2MSFTNG P11.phx.gbl...[color=blue]
> Greetings
>
> I'm having some problems on my C# application. I'm using an access
> database and I'm not able to do select queries with the ' character.
>
> My code is this:
>
> // some previous code like open connection
>
> string sqlStatement="S ELECT oid FROM formatos WHERE formato='cd\\'s '";
>
> myComm.CommandT ext=sqlStatemen t;
> OleDbDataReader myDR=myComm.Exe cuteReader();
>
> //... and so on
>
> I get the exception message
>
> AccessException : Syntax error (missing operator) in query expression
> 'formato='cd\'s ''.
> DeclaringType.F ullName:: System.Data.Ole Db.OleDbCommand
> Method - Void ExecuteCommandT extErrorHandlin g(Int32)
> at System.Data.Ole Db.OleDbCommand .ExecuteCommand TextErrorHandli ng(Int32
> hr)
> at
> System.Data.Ole Db.OleDbCommand .ExecuteCommand TextForSingleRe sult(tagDBPARAM S
> dbParams, Object& executeResult)
> at System.Data.Ole Db.OleDbCommand .ExecuteCommand Text(Object&
> executeResult)
> at System.Data.Ole Db.OleDbCommand .ExecuteCommand (CommandBehavio r
> behavior, Object& executeResult)
> at System.Data.Ole Db.OleDbCommand .ExecuteReaderI nternal(Command Behavior
> behavior, String method)
> at System.Data.Ole Db.OleDbCommand .ExecuteReader( CommandBehavior
> behavior)
> at System.Data.Ole Db.OleDbCommand .ExecuteReader( )
> at
> ...
>
>
> AccessError: Syntax error (missing operator) in query expression
> 'formato='cd\'s ''.
> Source: Microsoft JET Database Engine
> Native Error: -524553244
> State: 3075
>
> How can I escape the ' and " chars so I can insert them onto an Access
> database?
>
> TIA
>
> Lum[/color]

[Luminal]

Re: escape the ' and &quot; chars to insert them onto an Access database

Thanks Nicholas, I know that, but the way the application is done,
changing all the data layer statements/commands would take a long time.

Nicholas Paldino [.NET/C# MVP] wrote:
[color=blue]
> Luminal,
>
> Instead of trying to figure out the quotes yourself, I would recommend
> using a parameterized query, and then just set the value of the parameter to
> the value you have. The provider will take care of formatting the values
> correctly.
>
> Hope this helps.
>
>[/color]

[Nicholas Paldino [.NET/C# MVP]]

Re: escape the ' and &quot; chars to insert them onto an Access database

True, but at the same time, you are going to have to address the ones
that don't work anyways, so why not just change those?


--
- Nicholas Paldino [.NET/C# MVP]
- mvp@spam.guard. caspershouse.co m

"Luminal" <luminal_NOSPAM @hotpop_NOSPAM. com> wrote in message
news:u9r7xXwpEH A.3524@TK2MSFTN GP15.phx.gbl...[color=blue]
> Thanks Nicholas, I know that, but the way the application is done,
> changing all the data layer statements/commands would take a long time.
>
> Nicholas Paldino [.NET/C# MVP] wrote:
>[color=green]
>> Luminal,
>>
>> Instead of trying to figure out the quotes yourself, I would
>> recommend using a parameterized query, and then just set the value of the
>> parameter to the value you have. The provider will take care of
>> formatting the values correctly.
>>
>> Hope this helps.
>>[/color][/color]

[Jon Skeet [C# MVP]]

Re: escape the ' and &quot; chars to insert them onto an Access database

Luminal <luminal_NOSPAM @hotpop_NOSPAM. com> wrote:[color=blue]
> Thanks Nicholas, I know that, but the way the application is done,
> changing all the data layer statements/commands would take a long time.[/color]

It would be worth it. There are all kinds of reasons to use parameters
instead of embedding the values in SQL statements.

Better to do the work now than to get a SQL injection attack due to
some faulty quoting or formatting in your code.

--
Jon Skeet - <skeet@pobox.co m>

http://www.pobox.com/~skeet

If replying to the group, please do not mail me too

[Luminal]

Re: escape the ' and &quot; chars to insert them onto an Access database

Sure, I will take those consideration in future development :)

Thanks



Jon Skeet [C# MVP] wrote:
[color=blue]
> Luminal <luminal_NOSPAM @hotpop_NOSPAM. com> wrote:
>[color=green]
>>Thanks Nicholas, I know that, but the way the application is done,
>>changing all the data layer statements/commands would take a long time.[/color]
>
>
> It would be worth it. There are all kinds of reasons to use parameters
> instead of embedding the values in SQL statements.
>
> Better to do the work now than to get a SQL injection attack due to
> some faulty quoting or formatting in your code.
>[/color]

[James Curran]

Re: escape the ' and &quot; chars to insert them onto an Access database

> string sqlStatement="S ELECT oid FROM formatos WHERE formato='cd\\'s '";

You're escaping it the wrong way. The C# compiler is fine with the
unescaped string. You need to escape it for the SQL interpreter:
string sqlStatement="S ELECT oid FROM formatos WHERE formato='cd''s' ";
// that's single-quote, c, d, single-quote, single-quote, s, single-quote
(Then a double-quote to end the single)


--
Truth,
James Curran
Home: www.noveltheory.com Work: www.njtheater.com
Blog: www.honestillusion.com Day Job: www.partsearch.com
(note new day job!)
"Luminal" <luminal_NOSPAM @hotpop_NOSPAM. com> wrote in message
news:eKDKJHwpEH A.324@TK2MSFTNG P11.phx.gbl...[color=blue]
> Greetings
>
> I'm having some problems on my C# application. I'm using an access
> database and I'm not able to do select queries with the ' character.
>
> My code is this:
>
> // some previous code like open connection
>
> string sqlStatement="S ELECT oid FROM formatos WHERE formato='cd\\'s '";
>
> myComm.CommandT ext=sqlStatemen t;
> OleDbDataReader myDR=myComm.Exe cuteReader();
>
> //... and so on
>
> I get the exception message
>
> AccessException : Syntax error (missing operator) in query expression
> 'formato='cd\'s ''.
> DeclaringType.F ullName:: System.Data.Ole Db.OleDbCommand
> Method - Void ExecuteCommandT extErrorHandlin g(Int32)
> at
> System.Data.Ole Db.OleDbCommand .ExecuteCommand TextErrorHandli ng(Int32 hr)
> at
>[/color]
System.Data.Ole Db.OleDbCommand .ExecuteCommand TextForSingleRe sult(tagDBPARAM S[color=blue]
> dbParams, Object& executeResult)
> at System.Data.Ole Db.OleDbCommand .ExecuteCommand Text(Object&
> executeResult)
> at System.Data.Ole Db.OleDbCommand .ExecuteCommand (CommandBehavio r
> behavior, Object& executeResult)
> at
> System.Data.Ole Db.OleDbCommand .ExecuteReaderI nternal(Command Behavior
> behavior, String method)
> at System.Data.Ole Db.OleDbCommand .ExecuteReader( CommandBehavior
> behavior)
> at System.Data.Ole Db.OleDbCommand .ExecuteReader( )
> at
> ...
>
>
> AccessError: Syntax error (missing operator) in query expression
> 'formato='cd\'s ''.
> Source: Microsoft JET Database Engine
> Native Error: -524553244
> State: 3075
>
> How can I escape the ' and " chars so I can insert them onto an Access
> database?
>
> TIA
>
> Lum[/color]