use parameter in where which contain ' with select statement

Question

I want to use select query in SQL Server 2008 in which I want to search book's details according to their name.

I use

SELECT * 
FROM MasterTitle 
WHERE BookName = '"+s1''

and it works fine but problem occurs when s1 contains a quote '

Something like

SELECT * 
FROM MasterTitle 
WHERE BookName = 'Educational Cd'S (Set)' and DisplayAuthorName = 'Test'

I can't use escape character

*Exact duplicate*: http://stackoverflow.com/a/1586588/1563422 — Danny Beckett, Mar 20 '13 at 05:51

score 0 · Accepted Answer · answered Mar 20 '13 at 06:02

0

SELECT * 
FROM MasterTitle
WHERE BookName = 'Educational Cd'+char(39)+'S ( Set)' 
     and DisplayAuthorName ='Test'

answered Mar 20 '13 at 06:02

ljh

I cant use char(39) in middle of d string. because i used a parameter like 's1' for book name and it is entered by the user..and s1 may contain ' or may not. – Neha Mar 20 '13 at 06:07
you mean you use query like following:
```
select * from MasterTitle where BookName = @s1
```
, do you? because I don't understand how you use the s1 in your query; if you use BookName=@s1, then even you @s1 has ', it doesn't matter
– ljh Mar 20 '13 at 06:20
1

but if you use a dynamic sql construction, like sql = sql + s1, then if s1 has ' inside, it can cause problem, then you need to use escape, like replace(s1, char(39), char(39)+char(39)) – ljh Mar 20 '13 at 06:23

1 Answers1