CODEWITHSUNDEEP
c#ms-access
Gold
Gold

Reputation: 62424

How to write ' in SQL query?

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

Upvotes: 4

Views: 622

Answers (6)

Larsenal
Larsenal

Reputation: 51146

Single quotes can be escaped with two single quotes.

SELECT Fname FROM MEN WHERE Fnale = 'j''o'

Upvotes: 5

GvS
GvS

Reputation: 52518

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

Upvotes: 3

Hans Kesting
Hans Kesting

Reputation: 39274

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? :-) )

Upvotes: 2

Kamal
Kamal

Reputation: 2522

Try replacing ' with ''

Upvotes: 1

Alex
Alex

Reputation: 3652

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

Upvotes: 2

Pieter van Ginkel
Pieter van Ginkel

Reputation: 29632

Try a backslash \' or two quotes ''.

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

Upvotes: 9

Related Questions