4

i need to use the Characters ' in access query.

but if i write select Fname from MEN where Fnale = 'j'o' i get error

how to write the Characters '

thank's in advance

Pieter van Ginkel
  • 29,160
  • 8
  • 71
  • 111
Gold
  • 60,526
  • 100
  • 215
  • 315

6 Answers6

9

Try a backslash \' or two quotes ''.

This depends on your database. MySQL uses \' and Microsoft SQL and MS Access uses two quotes ''.

Community
  • 1
  • 1
Pieter van Ginkel
  • 29,160
  • 8
  • 71
  • 111
  • This probably speaks for itself, but just to elaborate: You need the \ so the character following will be interpreted as plain-text character. – Nick Nov 08 '10 at 09:28
5

Single quotes can be escaped with two single quotes.

SELECT Fname FROM MEN WHERE Fnale = 'j''o'
Larsenal
  • 49,878
  • 43
  • 152
  • 220
3

For SQL Server:

var cmd = new SqlCommand("select fname from MEN where fnale = @query", myConnection);
cmd.Parameters.AddWithValue("@query", "j'o");

All solutions where you add your parameter to the sql string yourself are wrong (or at least high risk), because they are vulnarable for a SQL Injection Attack.

You mention "access query", for Microsoft Access / Ole use the following syntax:

var cmd = new OleDbCommand("select fname from MEN where fnale = ?", myConnection);
cmd.Parameters.AddWithValue("?", "j'o"); // Order does matter
GvS
  • 52,015
  • 16
  • 101
  • 139
  • Have you given serious thought to what kind of real-world risk to SQL injection that there is with a Jet/ACE back end? It's a lot less than with most databases. I'm not disputing the advice in general, just pointing out that alarmism over SQL injection is mostly misplaced for your standard Access application. See http://stackoverflow.com/questions/512174/non-web-sql-injection/522382#522382 for my take on the subject. – David-W-Fenton Nov 08 '10 at 21:55
  • @David: I only have limited knowledge. A hacker might know a way to do a SQL Injection. I have some tools to make it harder, and I intend to use them. – GvS Nov 09 '10 at 07:42
  • It depends on what you mean by "SQL injection." Most people mean something a lot more drastic than what is possible with Access/Jet/ACE. – David-W-Fenton Nov 09 '10 at 22:06
2

I would use a literal string to avoid escaping everything

string query = @"select Fname from MEN where Fnale = 'jo'";

If you are escaping this with respect to SQL, then use another single quote to escape the quotes:

select Fname from MEN where Fnale = ''jo''
Alex
  • 3,644
  • 2
  • 19
  • 27
2

As others said, you can escape the quotes. But if you are sending that query from C#, then it's better to use parameters - that way all escaping is done for you, so you can't forget some special case where user input can still cause unwanted effects. (little bobby tables, anyone? :-) )

Hans Kesting
  • 38,117
  • 9
  • 79
  • 111
1

Try replacing ' with ''

Kamal
  • 2,512
  • 2
  • 34
  • 48