Cant pass search text into Stored Procedure

I am trying to inject dynamically generated text into a Sql2000 stored procedure. What am I doing wrong?
A code behind routine generates the following string value based on a visitor entering 'sail boats' in TextBox1. The routine splits the entry and creates the below string.

Companies.L_Keywords LIKE '%sail%' AND Companies.L_Keywords LIKE '%boats%'

I am trying to place this string result in the WHERE statement of a Sql2000 Stored Procedure using parameter @.VisitorKeywords.

PROCEDURE dbo.KWsearch
AS
SELECT DISTINCT Companies.L_Name, Companies.L_ID, Companies.L_Enabled
WHERE ( @.visitorKeywords ) AND (Companies.L_Enabled = 1)
ORDER BY Companies.L_Name

I am wanting the resulting WHERE portion to be:

WHERE ( Companies.L_Keywords LIKE '%sail%' AND Companies.L_Keywords LIKE '%boats%' ) AND (Companies.L_Enabled = 1)

Thank you

I noticed your starting character is '%something..'. Realize that this may not use any indexes on the L_Keywords column. Because your first character itself is a wildcard, SQL Server does not know where to start so it will not use the index. In order for indexes to be used the query should be like: 'something%'. Your query should look like this: Pass just the value to the proc. The value coming from your app for @.keyword1 would be '%sail%'.

SELECT DISTINCT Companies.L_Name, Companies.L_ID, Companies.L_EnabledWHERE ( Companies.L_KeywordsLIKE @.KeyWord1AND Companies.L_KeywordsLIKE @.KeyWord2 )AND (Companies.L_Enabled = 1)ORDER BY Companies.L_Name

|||

I was able to pass in the values but I need to pass in the entire string because the number of LIKE statements is determined by the number of keywords the user enters. If they enter "sail boats", I need two LIKE statements but if they enter "big blue sail boats" I need four LIKE statements. That is why I was trying to pass in the entire string.

|||

Is it possible to pass a partial SQL statement string into a stored procedure and concantenate it with the resident code using a parameter?

|||

Yes you'd have to use dynamic SQL. Check out this article before attempting to do so:http://www.sommarskog.se/dynamic_sql.html

|||

Below is the routine I am using to create the partial WHERE statement. Does it present a secuity risk? I assumed that programically generating the SELECT in this way would offer greatly improved security.

What I am trying to do is pass the strings produced by this routine into the Stored Procedure I use to access the database.

protectedvoid KeyWordSqlSelect()
{
kwdString = UserKeyword.Text;
Criteria1 ="";
Criteria2 ="";
string[] ArrKwdString = System.Text.RegularExpressions.Regex.Split(kwdString," ");
int iCount = 0;
foreach (string sValuein ArrKwdString)
{
Criteria1 +=string.Format("tblCompany.L_Keywords LIKE '%{0}%'", sValue);
Criteria2 +=string.Format("tblFranchise.L_Keywords LIKE '%{0}%'", sValue);
if ((iCount + 1) < ArrKwdString.Length)
Criteria1 +=" AND ";
Criteria2 +=" AND ";
iCount += 1;
CKWsearch = Criteria1;
SKWsearch = Criteria2;
TextBox1.Text = Criteria1;
TextBox2.Text = Criteria2;
}

}

|||

Please check out the article link I posted. There's risks/performance issues that you need to get an understanding of with dynamic SQL.

|||

I read the article but do not see what risk you are suggesting. If the partial WHERE string is generated within the C# code I do not see the injection risk unless there is a problem with the " " space delimiter used in the Split method for the incoming keyword string.

|||

The biggest risk is the SQL Injection attack. Performance issues include bad query plans, not storing query plans if you dont use sp_Executesql etc.

|||

I somewhat understand what you are talking about now (only after reading the article three times). I have abandoned the stored procedure and now am generating the Select statements in the code behind accessing them directly using SqlCommand object. I am hoping that will protect me from injection attacks as none of them are exposed to the users. Thank you for the expert advice. Sql is not a place for amateurs like myself to be mucking about. Your sage advice is greatly appreciated.

|||

Welcome. Glad to help.