Single Quote problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Single Quote problem

Author

Topic

kim12345
Starting Member

5 Posts

Posted - 2008-08-07 : 07:26:26

Hi Folks,

I have a query ,the parameter will be passed to the query as a string at runtime.

Ex:select *from customers where cusaddress='$customeraddress'

sometimes the customeraddress value may have single quotes.

how can i resolve this problem..?

I would be appreciate your help.

Thanks

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-07 : 07:50:13

Check out a function called QUOTENAME() in books online.

Basically you need to escape the ' which is SQL is done by adding a second one.

So if you want to insert the value "This isn't so bad" into a variable

You'd do :: SET @variable = 'This isn''t so bad'

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-07 : 07:55:17

Actually, reading you requirements this might not be a problem.


DECLARE @test TABLE (
	[fielda] VARCHAR(255)
)

INSERT INTO @test
      SELECT '5 OLDHALL DRIVE, TOWNVILLE, COUNTY O''HARE'
UNION SELECT 'some other string'

DECLARE @testString VARCHAR(50)
SET @testString = '5 OLDHALL DRIVE, TOWNVILLE, COUNTY O''HARE'

-- Show the string (note the quote)
PRINT @testString 

SELECT * FROM @test WHERE [fielda] = @testString

Will work.

-------------
Charlie

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-08-07 : 08:08:44

Problems may arise if you have a customer living at ';DROP TABLE Customers;--
Do you know about SQL-injection?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-07 : 08:19:31

Yes. Don't blindly add the string to some dynamic sql.

From your original post though there is no risk of sql injection if you are using the variables as part of a where clause in static sql.

Given the string

@input = "5 Ashdown Road'; DROP TABLE customers"

If you do

SELECT * FROM customer WHERE address = @input

Then you will be fine...

However, if your query is really this...

SET @sql = ',......
.....

SELECT * FROM customer WHERE address = ' + @inupt

EXEC (@sql)

Then you are going to be in a world of hurt. Validate those inputs!

-------------
Charlie

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-07 : 08:24:21

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx

Madhivanan

Failing to plan is Planning to fail

kim12345
Starting Member

5 Posts

Posted - 2008-08-07 : 08:32:09

Ex:select *from customers where cusaddress='$customeraddress'
if I pass "OldOxRd Maiso's" as aa string at runtime
it's building query as....
select *from customers where cusaddress='OldOxRd Maiso's'
and getting syntax error.
so,is any way to replace that single quote in the query dynamically.

I don't know about sql injection.

Thanks

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-08-07 : 08:43:22

Do you pass the whole query to the database?
What's wrong with replacing single quotes with two of them, as Charlie mentioned?

kim12345
Starting Member

5 Posts

Posted - 2008-08-07 : 08:58:29

I don't have a control on the string formation at codelevel.
actually i need to put the query in the databse table.that query will be used by java code.java code only pass the value as the parameter to the my query.so i need to handle the problem in same query dynamically.

Thanks

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-07 : 10:41:22

Well as I said, use quotename() or replace.

Please post your query for more specific help but what I think you are doing is building a string in a variable

So are you doing something like this.


DECLARE @paramater VARCHAR(255)
SET @paramater = 'foo''00'

DECLARE @sql VARCHAR(8000)

SET @sql = 'SELECT * FROM customers WHERE address = ' + @paramater

EXEC (@sql)

If you are change to this instead.


DECLARE @paramater VARCHAR(255)
SET @paramater = 'foo''00'

DECLARE @sql VARCHAR(8000)

SET @sql = 'SELECT * FROM customers WHERE address = ' + QUOTENAME(@paramater,'''')

PRINT @sql

EXEC (@sql)

Obviously @paramater is not build by you in reality but passed in you store proc or whatever you are using.

QUOTENAME will safely escape the ' character for you.

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-07 : 10:43:31

Oh, and follow Madivanan's link if you haven't already

quote:

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx

Madhivanan

Failing to plan is Planning to fail

-------------
Charlie

kim12345
Starting Member

5 Posts

Posted - 2008-08-07 : 12:03:09

Charlie,

I am using single query only.
even using quote also I am getting error...
I used as following...
select *from customers where address=QUOTENAME('$address','''');
at runtime it's executing as....
select *from customers where address=QUOTENAME('King's Street','''');
even in replace function also
select *from customers where address=Replace('King's Street',''','''');

and getting error.
pls give me solution to resolve in single query.

Thanks

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-07 : 14:14:02

Hi kim12345,

I don't think I can help you any more unless you post your complete code.

I can't understand from your description

quote:

I am using single query only.
even using quote also I am getting error...
I used as following...
select *from customers where address=QUOTENAME('$address','''');
at runtime it's executing as....
select *from customers where address=QUOTENAME('King's Street','''');
even in replace function also
select *from customers where address=Replace('King's Street',''','''');

and getting error.
pls give me solution to resolve in single query.

where this code is getting executed.

Can you describe how the paramater (The address) is entered by your user or whoever.

Are you making a stored procedure? If not you should.

Are you building the compete string in some sort of application and then trying to run that somehow? I'm troubled by your "$address" as this doesn't look like a TSQL variable.

Also -- if you say "and getting error." please, please post the error text as well. It's very hard to difinitively answer you because we don't have the information you have.

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-07 : 14:17:59

Actually I think I know what's happeneing.

I think you'll need to use whatever replace methods are available to you in the programming language you are using. The problem is not a SQL one I don't think you are even getting that far.

What programming language are you using to make your query?

-------------
Charlie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources