SQL Injection

[Chris]

Hi,

I am trying to create a database procedure that will produce a list of
results based on a Google like search.

e.g. "Frank Spencer" -car +BBC

I would normally produce a dynamic where clause something like this:-
select title from videos where title like '%Frank Spencer%' and title like
'%BBC%' and title not like '%BBC%'
but I believe that would be open to a sql attack and I should be using
parameters.

I could create a parameter for each but I do not know how many words will be
entered and then I need to evaluate the + and - signs to be like and not
like.

I'm sure this gets done all the time so if anyone can supply a url of an
example I would be grateful.

Thanks

Chris

[infi...@gmail.com]

Hi Chris,

> I would normally produce a dynamic where clause something like this:-
> select title from videos where title like '%Frank Spencer%' and title like
> '%BBC%' and title not like '%BBC%'
> but I believe that would be open to a sql attack and I should be using parameters.

In your case do not use the user data directly,
first pass that data in a function and pass into query

Your Query will:

Select title from videos where title like
dbo.FNAGetValidString('%Frank Spencer%') and title like
'%BBC%' and title not like dbo.FNAGetValidString('%BCC%')

---------Function Script you can add other unwanted charectar

Alter FUNCTION FNAGetValidString(@Str as varchar(200))
RETURNS varchar(20) AS
BEGIN
set @Str=replace(@Str,'--','')
set @Str=replace(@Str,'-','')
set @Str=replace(@Str,'+','')
set @Str=replace(@Str,'drop+','')

return @Str
END

Hope this will Help you !

MCP
Raghu Bhandari