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.

Can't I dynamically read from any file? - Connection Manager does not exist

I have a Parent package and a child package.

I have hard coded the value for the User::FilePath variable in the parent package.

I am mapping this variable to the value of the same variable in the Child Package.

I created a directory and sql file: "C:\temp\sqlb.sql". I have verified that the path variable value is passed to the child package by using a Script task with a messgbox call.

How do I define an execute sql task to execute sql file: @.[User::FilePath] & "sqlb.sql". I'm using this expression for the SqlStatementSource property. I have entered the OLEDB server information and specified the SQLSourceType = fileconnection.

However I get the error:

[Execute SQL Task] Error: Connection manager "D:\rlewisdev\ZZ\sqlb.sql" does not exist.

What am I doing wrong?

SQLStatementSource in this case points to a connection manager, not a file. You'll have to build a connection manager, call it "SQLFile" or something and use that as the SQLStatementSource. Then you'll have to change the ConnectionString of the connection manager named "SQLFile" (or whatever you decide to call it.)|||

Hi boston..,

You have to create a connection manager based on file path,

you can do the following steps:

1-create connection manager myfilepath

2-right click on this connection and click properties

3-in properties click on the button ... behind expression property

4-In property expression editor ,select the connection string property, and click button ... in the right column

5-In expression builder window expand the variable tree and select ur path variable and drag it to the expression container.

6-click on evaluate expression

NB: you can save your configuration setting in xml file or regsitry or sql server from "package configuration wizard".

Thanks,

Tarek Ghazali

SQL Server MVP

http://www.sqlmvp.com