Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 195
  • Last Modified:

Using free-text criteria in a stored procedure, possible ?

For maximum flexibility I have users that have asked for ability to be able to just have a textbox and be able to filter themselves. This seems easy but for some reason it will not compile. I made a varchar with length of 255 and of course it does not like having the variable after the 'where' statement. There has to be some way of tricking it into compiling, because the logic will work.

CREATE PROCEDURE mydatabase.text

@FREETEXT VARCHAR(255)

AS

SELECT LAST, FIRST FROM TABLE
WHERE @FREETEXT

This is totally fake but does resemble the real command. I totally understand why it complains about it but there has to be a way of fooling it.

0
ParanoidOne
Asked:
ParanoidOne
  • 2
1 Solution
 
ParanoidOneAuthor Commented:
From reading it seems this method is higly frowned upon because it leads to injection attacks. I will say that this database has no outside access and only 3 people accessing it. The only person that would do free text database stuff would be one user that is fairly skilled. Should I go back to straight select statements ?
0
 
t_itaniumCommented:
what are you trying to do...

if you are comparing the freetext to some field in the database... you can simply use

.....

WHERE field=@FREETEXT

idont know if that what you want

cheers
0
 
doobdaveCommented:
Hi there,

What you are trying to achieve can be done using "Dyamic SQL".

As you have read, this can be a dangerous thing to use, as it can lead to injection attacks, and is also not the best performing.

However, given that only one user will have the ability to do this, and you mention he is highly skilled, here is how you use dynamic sql (do some reading on it also in the Books Online and MSDN).

CREATE PROCEDURE mydatabase.text

@FREETEXT NVARCHAR(4000)

AS

DECLARE @Sql NVarChar(4000)

SET @Sql = 'SELECT LAST, FIRST FROM TABLE WHERE ' + @FREETEXT
EXEC(@Sql)

Obviously, if the user passes through some invalid garbage, the execute will return an error.

Hope this helps you.

Best Regards,

David
0
 
ParanoidOneAuthor Commented:
I am going to accept it since you made it so easy to read, but probably just going back to a select statement since they are so much easier to do.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now