Movin dynamic inline SQL to Sproc - How ?

Last Post 05 Jun 2006 02:03 AM by mwesch. 14 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
five40
New Member
New Member

--
11 May 2006 01:29 AM
I have a bunch of old apps which use dynamic SQL clauses and which are part of the ASP-page itself.

Moving them to ASP.NET 2.0 world ....

I have a DAL which my upgraded .NET app uses and for basic SQL statements I use stored procedures. This works fine for simple data-access, but I have no idea how to create dynamic stored procedures. Can I use IF-clauses in Stored Procedures as well ? What I mean is that when I have bunch of fields on a app and I want to use those fields as search criteria. This could be done easily in ASP.OLD world but also in .NET apps too. But I want to get rid of the inline SQL. Any advice ?
mwesch
New Member
New Member

--
11 May 2006 02:25 AM
You might be confusing a couple topics, making it sound more complicated than it really is. A "dynamic" stored procedure isn't the same thing as a "parameterized" stored procedure. If you only want to pass in a few search criteria from a a web app then a parametrized stored procedure will do the rick. The process is really the same, whether ASP or ASP.NET.

Here's a simple stored procedure that would accept a search parameter and return a list of recent sales orders.

---------------------------------------------------
create procedure pr_GetCustomerSalesOrders @CustomerID int
as

select CustomerID, SalesDate, ProductID, Quantity, UnitCost, TotalAmount
from CustomerSales
where CustomerID = @CustomerID
---------------------------------------------------

In ASP look at using ADO Command; in ASP.NET use ADO.NET Command. The Command object allows you to call the stored procedure, adding the search criteria as parameters.
five40
New Member
New Member

--
15 May 2006 09:11 PM
I still don't get it. How do I construct the SQL query at the Stored Procedure's end ?

So let's say that I have three fields, firstname,lastname and city I would like to query. Based on if one of the fields is filled on the web form, that field would be used as search criteria. In the old world I would be doing the inline version like this:

SELECT * FROM Customers WHERE SomeCriteria = Something

IF txtFirstName <> "" Then

AND FirstName = txtFirstName

END IF

IF txtLastName <> "" Then

AND LastName = txtLastName

END IF

IF txtCity <> "" Then

AND City = txtCity

END IF

and so on ...

The question remains, how do I move this to a Stored Procedure ? Or if this can be done using Command Object, I'm more than interested to know how to accomplish this.
cmdr_jpskywalker
New Member
New Member

--
15 May 2006 11:22 PM
I am not sure what you're asking but I' ll try. In SQL Server end,

CREATE PROCEDURE TEST
AS
BEGIN
DECLARE
@LastName VARCHAR(100),
@FirstName VARCHAR(100)

--if stmt here
SET @LastName = 'Test'
SET @FirstName = 'Test'

SELECT *
FROM CUSTOMER
WHERE LastName = @LastName
AND FirstName = @FirstName

END

In VB, you specify the commandtext attribute to "Test". You can change the variables into parameters if you want so that you can specify the parameter values in VB (ie CREATE PROCEDURE TEST (@LastName VARCHAR(100), @FirstName VARCHAR(100)..)
nosepicker
New Member
New Member

--
16 May 2006 07:41 AM
I think you want to do something like this:

CREATE PROCEDURE YourProc
@firstname varchar(50) = NULL,
@lastname varchar(50) = NULL,
@city varchar(50) = NULL
AS

IF @firstname IS NOT NULL
SELECT * FROM customers WHERE firstname = @firstname

IF @lastname IS NOT NULL
SELECT * FROM customers WHERE lastname = @lastname

IF @city IS NOT NULL
SELECT * FROM customers WHERE city = @city


There are other ways to do this - this is just one basic method. You would call the procedure like this:

EXECUTE YourProc NULL, 'LastNameValue', NULL
or
EXECUTE YourProc @lastname = 'LastNameValue'

if the lastname was chosen from the web form.

SQLUSA
New Member
New Member

--
18 May 2006 04:58 AM
Check out this article: http://www.sqlusa.com/bestpractices/dynamicsql/" target="_blank" rel="nofollow">http://www.sqlusa.com/bestpractices/dynamicsql/



Kalman Toth, Database, Data Warehouse and BI Architect
http://www.sqlusa.com
The Best SQL Server 2005 Training in the World
five40
New Member
New Member

--
04 Jun 2006 05:13 AM
OK, so this is the way I got if working:

CREATE PROCEDURE MyProcedure (
@CompanyName varchar(50) = null,
@ContactName varchar(50)= null,
@ContactTitle varchar(50)= null,
@City varchar(50)= null
)

AS

BEGIN
DECLARE @SQL varchar(500)

SELECT @SQL = 'SELECT CompanyName, ContactName, ContactTitle, City FROM Customers WHERE 1=1' +

CASE
WHEN @CompanyName IS NOT NULL and @CompanyName <> ''
THEN ' AND CompanyName = ''' + @CompanyName + ''''
ELSE ''
END +

CASE
WHEN @ContactName IS NOT NULL and @ContactName <> ''
THEN ' AND ContactName LIKE ''%'+ @ContactName + '%'''
ELSE ''
END +

CASE
WHEN @ContactTitle IS NOT NULL and @ContactTitle <> ''
THEN ' AND ContactTitle LIKE ''%'+ @ContactTitle + '%'''
ELSE ''
END +

CASE
WHEN @City IS NOT NULL and @City <> ''
THEN ' AND City LIKE ''%'+ @City + '%'''
ELSE ''
END

EXECUTE(@SQL)
END
GO

If all the ASP.NET form fields are empty, all the fields are returned. Otherwise the SQL is generated on the fly. By this way I don't have to use inline SQL in my aspx.cs -code anymore.
mwesch
New Member
New Member

--
04 Jun 2006 06:05 AM
Ultimately though, you are still building dynamic SQL based on user input. The ASP.NET code may be cleaner, but it isn't really any more secure from things like SQL injection.

You might try this:

----------------------------------------------------------------
CREATE PROCEDURE MyProcedure
@CompanyName varchar(50) = null,
@ContactName varchar(50)= null,
@ContactTitle varchar(50)= null,
@City varchar(50)= null
AS

select CompanyName, ContactName, ContactTitle, City
from Customers
where (@CompanyName is null or CompanyName like '%' + @CompanyName + '%')
and (@ContactName is null or ContactName like '%' + @ContactName + '%')
and (@ContactTitle is null or ContactTitle like '%' + @ContactTitle + '%')
and (@City is null or City like '%' + @City + '%')
five40
New Member
New Member

--
04 Jun 2006 08:19 PM
Ok, thanks. I have to take a look at this.
five40
New Member
New Member

--
04 Jun 2006 08:57 PM
Thanks for the code, it works. But what is actually making this code more secure ?
five40
New Member
New Member

--
04 Jun 2006 10:40 PM
Well, it works and it doesn't. The simple code against Northwind is OK but then I tried this somewhere else. I don't get anything back from DB ... I have checked that the parameters names are excatly the same in my procedure than in my ASP.NET app. In my real world scenario there are 15 form fields / dropdowns used. I'm getting no exceptions from the code so the data is sent to procedure from ASP.NET app.

I tried this with just two form object, 1 textbox and 1 dropdown. Searching the DB by just using the value retrieved from dropdown works. Whenever I try to fine grain the search by adding some text to form field (it should use LIKE condition in that), nothing is returned. Also, nothing is returned if I leave the form values (in dropdown or in textbox not set), this should return all records from the database in this case. I also checked that the value I was entering to textbox is actually found on the db table.

See this code:

WHERE (@Field1 IS null OR Field1 = @Field1) 'data retrieved from a dropdown menu
AND (@Field2 IS null OR Field2 LIKE '%' + @Field2 + '%') 'data retrieved from an textbox

Any ideas how to fix this ? OR should I still use SELECT CASEs here ?
cmdr_jpskywalker
New Member
New Member

--
04 Jun 2006 10:47 PM
if you have a parameter which is similar to this
@CompanyName = '''; DROP DATABASE master;--'

In your query, say goodbye to your master database. In mwesch, at worst, it will generate an error. But since no syntax error, it will return nothing.

See the SQL Injection for more info.
mwesch
New Member
New Member

--
05 Jun 2006 02:03 AM
I would also check to ensure you are sending to the database what you think you are, and possibly datatypes.

For dropdown menu, could you be sending the Description of the field, where the field value ia actually a numeric field.

For textbox, are datatypes TEXT in the database? If so, this won't work.


To test that the code works just write a quick script in QA.

------------------------------------------------

declare @Field1 int, @Field2 varchar(10)
select @Field1 = 5, @Field2 = null
--select @Field1 = null, @Field2 = 'some text'

select *
MyTable
where (@Field1 is null or Field1 = @Field1)
and (@Field2 is null or Field2 like '%' + @Field2 + '%')
five40
New Member
New Member

--
05 Jun 2006 03:12 AM
About SQL injection:

This is the way I read the values in my ASP.NET app:

SqlCommand cmd = new SqlCommand("MyProcedure", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cnn.Open();


// Add Parameters
cmd.Parameters.Add(new SqlParameter("@Field1", SqlDbType.VarChar, 20));
cmd.Parameters["@Field1"].Value = myDropDown.SelectedItem.Value;

cmd.Parameters.Add(new SqlParameter("@Field2", SqlDbType.VarChar, 20));
cmd.Parameters["@Field1"].Value = myTxtField.Text.Trim();

... and so on

So even that won't prevent SQL Injection ? Meaning, isnt the parameter values treated as literal instead of executable code ?

There are also checks made on my code so that form fields are validated before submitting them.
mwesch
New Member
New Member

--
05 Jun 2006 05:21 PM
The point about SQL injection is that no matter how the info gets to SQL Server, that if you ultimately build dynamic SQL based on user input that it could result in an undesirable SQL statement being built.

Even with your input validation, if you allow a text search field that is 50 characters long then someone could enter the following as search criteria.

; DROP DATABASE master;--

The semicolon would end one SQL statement, the drop database command would runn, and then the two dashes would comment out anything that follows.

Certainly input validation can help, but I suspect you are not doin such extensive input validation to check for malicious SQL.
You are not authorized to post a reply.

Acceptable Use Policy