| Author |
Topic |
|
haroon2k9
Constraint Violating Yak Guru
328 Posts |
 Posted - 2010-02-23 : 05:16:26
|
| Hi,Please see below sproc,This is for string comparison for a given table,cloumn name and string to compare against it.The below sproc is comparing ok(Means whether the string already exists in a Tbl or not) for a string like (abc,usa,india) and for(abc')not able to compare.could you please help me,how to compare this kind of string with apostrophe against a table. ALTER PROCEDURE [dbo].[Stringcomparison_sp] ( @TableName varchar(255), @ColumnName varchar(255), @CompareString varchar(255) ) As BEGIN Declare @table varchar(255) Declare @cmpstring varchar(max) Declare @andclause varchar(50) Declare @SQL nvarchar(max) set @table = @TableName set @cmpstring =' Where ' + @ColumnName + ' = ''' + @CompareString + '''' set @SQL = 'SELECT COUNT(1) FROM ' + @table + @cmpstring exec (@SQL) END |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-02-23 : 05:22:36
|
You can mask a single quote by a second single quote.declare @cmpstring varchar(255)set @cmpstring = 'abc'''select @cmpstring select replace(@cmpstring,'''','''''')
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-23 : 05:28:39
|
| set @cmpstring =' Where ' + @ColumnName + ' = ''' + REPLACE(@CompareString, '''', '''''') + '''' But better to use sp_ExecuteSQL [i.e. instead of EXEC] (which does not have this problem) and will cache the Query Plan so be far more efficient. |
|
|
|
haroon2k9
Constraint Violating Yak Guru
328 Posts |
Posted - 2010-02-23 : 05:31:26
|
quote:
Originally posted by webfred
You can mask a single quote by a second single quote.declare @cmpstring varchar(255)set @cmpstring = 'abc'''select @cmpstring select replace(@cmpstring,'''','''''')
No, you're never too old to Yak'n'Roll if you're too young to die.
Sorry.This solution was not comparing my string. |
|
|
|
haroon2k9
Constraint Violating Yak Guru
328 Posts |
Posted - 2010-02-23 : 05:50:48
|
quote:
Originally posted by Kristen
set @cmpstring =' Where ' + @ColumnName + ' = ''' + REPLACE(@CompareString, '''', '''''') + ''''
Thank you very much Kristen.Works like a charm.quote:
But better to use sp_ExecuteSQL [i.e. instead of EXEC] (which does not have this problem) and will cache the Query Plan so be far more efficient.
Thank you for your advice.i changed to EXECUTE sp_executesql @SQL instead of (exec(@sql)) for sproc. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-02-23 : 05:51:23
|
quote:
Originally posted by haroon2k9
quote:
Originally posted by webfred
You can mask a single quote by a second single quote.declare @cmpstring varchar(255)set @cmpstring = 'abc'''select @cmpstring select replace(@cmpstring,'''','''''')
No, you're never too old to Yak'n'Roll if you're too young to die.
Sorry.This solution was not comparing my string.
That's right - I know.I thought only to give a pointer in the right direction...
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
haroon2k9
Constraint Violating Yak Guru
328 Posts |
Posted - 2010-02-23 : 05:54:10
|
quote:
Originally posted by webfred
quote:
Originally posted by haroon2k9
quote:
Originally posted by webfred
You can mask a single quote by a second single quote.declare @cmpstring varchar(255)set @cmpstring = 'abc'''select @cmpstring select replace(@cmpstring,'''','''''')
No, you're never too old to Yak'n'Roll if you're too young to die.
Sorry.This solution was not comparing my string.
That's right - I know.I thought only to give a pointer in the right direction...
No, you're never too old to Yak'n'Roll if you're too young to die.
Oh!.Okay.Thanks webfred. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-23 : 05:57:48
|
| It wasn't supposed to. It was an example to show you how to use REPLACE in SQL. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
haroon2k9
Constraint Violating Yak Guru
328 Posts |
Posted - 2010-02-23 : 06:11:15
|
quote:
Originally posted by Kristen
It wasn't supposed to. It was an example to show you how to use REPLACE in SQL.
set @cmpstring =' Where ' + @ColumnName + ' = ''' + REPLACE(@CompareString, '''', '''''') + '''' but it was comparing my string.i wonder |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-23 : 06:28:57
|
| Sorry, my reply was late - the phone rang whilst I was writing it and I then posted it without being able to see other people had already replied. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-02-23 : 06:32:31
|
| Also you need to read about SQL InjectionMadhivananFailing to plan is Planning to fail |
|
|
|
haroon2k9
Constraint Violating Yak Guru
328 Posts |
Posted - 2010-02-23 : 06:38:41
|
quote:
Originally posted by madhivanan
Also you need to read about SQL InjectionMadhivananFailing to plan is Planning to fail
Thanks madivanan.In my case,we dont face sql injection kind of problems,cos we are using parameterized queries from the front end.i had only problem with the string comparison like apostrophe used with dynamic sql, which i was mentioned in my post above. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-23 : 06:50:54
|
| " cos we are using parameterized queries from the front end."... and then you are making concatenated-SQL in the backend and re-introducing the problem.Doing that has reintroduced the problem, and taken away all the protection of parameters in the first place.Although if you use REPLACE, as above, on ALL (and I mean absolutely every one, including numbers) variable data used in your adhoc queries AND surround the data with single quotes, then you will not have an issue."i changed to EXECUTE sp_executesql @SQL instead of (exec(@sql)) for sproc"That won't give you the protection, you need to parametrise it (and you are still going to have the problem with the Table Name (which you can't parametrise) - although if that has come from your application [i.e. not from the User] you may consider it safe. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-02-23 : 07:06:41
|
| QUOTENAME is much easier to use and understand than the multiple replaces.For table names in string injections do something likeSET @sql = N'USE ' + QUOTENAME(@tableName) + '......'To escape signle quotes:QUOTENAME(@string, '''')Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-23 : 09:44:15
|
| Hmmm ... I think that using QUOTENAME for dynamic SQL "parameters" is relying on effects of that function but using it in a purpose that it was not designed for, and that may have unintended consequences.e.g. limit of string that can be handled is 128 characters - that is likely to catch folk out and leave them scratching their heads for a while!But it certainly does the right job of surrounding the variable with quotes, and escaping any embedded quotes.All-in-all my preference would be for a UDF and then use it religiously for all dynamic SQL.QUOTENAME is just right for table / column names though |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-02-23 : 10:28:22
|
quote:
Originally posted by Kristen
Hmmm ... I think that using QUOTENAME for dynamic SQL "parameters" is relying on effects of that function but using it in a purpose that it was not designed for, and that may have unintended consequences.e.g. limit of string that can be handled is 128 characters - that is likely to catch folk out and leave them scratching their heads for a while!But it certainly does the right job of surrounding the variable with quotes, and escaping any embedded quotes.All-in-all my preference would be for a UDF and then use it religiously for all dynamic SQL.QUOTENAME is just right for table / column names though
Yes. Thats why I didn't suggest usage of QUOTENAME in my blog post  MadhivananFailing to plan is Planning to fail |
|
|
|
haroon2k9
Constraint Violating Yak Guru
328 Posts |
Posted - 2010-02-24 : 06:53:56
|
quote:
Originally posted by Kristen
" cos we are using parameterized queries from the front end."... and then you are making concatenated-SQL in the backend and re-introducing the problem.Doing that has reintroduced the problem, and taken away all the protection of parameters in the first place.Although if you use REPLACE, as above, on ALL (and I mean absolutely every one, including numbers) variable data used in your adhoc queries AND surround the data with single quotes, then you will not have an issue."i changed to EXECUTE sp_executesql @SQL instead of (exec(@sql)) for sproc"
Sorry for my late reply.Thanks kristen..As i mentioned earlier in this post like,iam just passing tablename,columnname and string to be compared against a DB as a Parameterized query to DB,then using dynamic sql to compare the string with single quotes had got a issue(Dirty approach)but we have many scenerios in the front end which has to be a compare the string aginst the given input params for a Db.so i had folowed this approach(Dynamic sql).i had sorted this issue(Sigle quotes)by adding Replace function with your help..quote:
That won't give you the protection, you need to parametrise it (and you are still going to have the problem with the Table Name (which you can't parametrise) - although if that has come from your application [i.e. not from the User] you may consider it safe.
U spotted out problem clearly..could you please suggest me the best way of doing this?.. |
|
|
|
haroon2k9
Constraint Violating Yak Guru
328 Posts |
Posted - 2010-02-24 : 10:01:50
|
| Any body can suggest me the best work around for this above please?Thanks. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-24 : 11:43:59
|
| Use sp_ExecuteSQL with a parametrised query (containing dynamic SQL for the Table and Column names, escaped for "[...]", using QUOTENAME, and parameters for any other data.See SQL Documentation for examples. |
|
|
|
haroon2k9
Constraint Violating Yak Guru
328 Posts |
Posted - 2010-02-24 : 23:02:19
|
Thank you very much for your response kristen.sorry to say, i dint understand really....quote:
escaped for "[...]", using QUOTENAME, and parameters for any other data.
|
|
|
|
Next Page
|
Check the string with the Apostrophe for a Table