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="SELECT oid FROM formatos WHERE formato='cd\\'s'";

myComm.CommandText=sqlStatement;
OleDbDataReader myDR=myComm.ExecuteReader();

//... and so on

I get the exception message

AccessException: Syntax error (missing operator) in query expression
'formato='cd\'s''.
DeclaringType.FullName:: System.Data.OleDb.OleDbCommand
Method - Void ExecuteCommandTextErrorHandling(Int32)
at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)
at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS
dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object&
executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior
behavior, Object& executeResult)
at
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior
behavior)
at System.Data.OleDb.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

 

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

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.

 

[Mythran]

With access use the following string:

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

Try that

Mythran

 

[Luminal]

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

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?

 

[Jon Skeet [C# MVP]]

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

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.

 

[Luminal]

Sure, I will take those consideration in future development

Thanks

 

[James Curran]

string sqlStatement="SELECT 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="SELECT 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!)