SQL Injection and Dynamic SQL

The consensus on avoiding SQL injection attacks is to pass all your collected data to a stored procedure.  If you have to return a result set from a complex form, the answer seems to be to use dynamic SQL.  On further study, I discovered that dynamic SQL has a lot of potential pitfalls and low and behold can still be vulnerable to injection attacks.  I know that I am not the first person to ever try and collect data from a web form and return a result set, so what are the best practices?  Say I have a complex form with about 25 search criteria (most of which are optional), I go through all the trouble of setting up each parameter and get them to a stored procedure, what do I do now to create a dynamic SQL statement that can first handle a whole bunch of optional parameters and then not be susceptible to an injection attack?

Does anyone have any examples, real world preferable?
0
LearningCurve
9/13/2004 11:26:58 PM
asp.net.object-datasource 16182 articles. 0 followers. Follow

5 Replies
806 Views

Similar Articles

[PageSpeed] 53

the most common SQL Injection attacks revolve round the use of the single quote character

(')
always make sure you escape single quotes by doubling them (''). this will stop that variant in its tracks
another variant revolves around exploiting numeric input - the solution there is to always validate input variables that are meant to be numeric and make sure they actually are. I use int.Parse() or float.Parse() mostly for this.
if you must use dynamic SQL, always make sure your input is validated.
and while we're on the subject, it's usually a very good idea to make sure HTML can't be injected either - if it can then you're probably vulnerable to cross-site scripting exploits, which can be used to spectacular effect to hijack entire Sessions (I've seen it demoed and was scared enough to spend the next two evenings autiting code for XSS vulnerabilities)
RTFM - straight talk for web developers. Unmoderated, uncensored, occasionally unreadable

Jason Brown - MVP, IIS
0
Atrax
9/14/2004 1:14:38 AM
So you are saying that you don't use dynamic SQL in a stored proc, you do everything in the website?  What other precautions do you take then?
0
LearningCurve
9/14/2004 2:04:35 AM
no, that's not what I'm saying. dynamic SQL in a stored proc is just as vulnerable to injection as scripted strings if you don't use caution.
RTFM - straight talk for web developers. Unmoderated, uncensored, occasionally unreadable

Jason Brown - MVP, IIS
0
Atrax
9/14/2004 8:24:08 AM

Learningcurve.
------------------
there a no problemi.
objCommand.CommandText="SELECT * FROM table1 WHERE ID=@ID";
objCommand.Parameters.Add("@ID",SqlDbType.Int).Value=ID;
SqlDataReader KundUppgiftReader=objCommand.ExecuteReader(CommandBehavior.CloseConnection);
This is a parameterized SQL and its built dynamically.
U DONT have to use a SP to do parameterized questions.
And one more thing. SP´s isnt faster than T-SQL nowadays.
since SQL Server 2000 and SQL Server version 7.0 BOTH T-SQL and SP´s is compiled into an execution plan and saved into the cache.
and gets used over and over again.
read about it here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_0nxv.asp
"Stored Procedures and Execution Plans"
0
badbanana
9/14/2004 12:53:00 PM
::And one more thing. SP´s isnt faster than T-SQL nowadays. 

::since SQL Server 2000 and SQL Server version 7.0 BOTH T-SQL and SP´s is compiled
::into an execution plan and saved into the cache.
::and gets used over and over again.

I'm not sure you really mean this-or maybe I'm just misunderstanding you. Stored procedures are faster than t-sql built into the webpage. The article you link to says so:
"Stored procedures can also improve performance. Many tasks are implemented as a series of SQL statements. Conditional logic applied to the results of the first SQL statements determines which subsequent SQL statements are executed. If these SQL statements and conditional logic are written into a stored procedure, they become part of a single execution plan on the server"
"Politicians never accuse you of 'greed' for wanting other people's money --- only for wanting to keep your own money." [Joseph Sobran]
0
FatHead
9/15/2004 4:21:15 PM
Reply:

Similar Artilces:

Avoiding SQL Injection with Dynamic SQL
I am exclusively using Stored Procedures to access the database, i.e. there are no Ad-Hoc SQL statements anywhere in the C# code. However, one thing I need to be able to do is to allow filtering for data grids on my ASP.NET page. I want to do the filtering in the Stored Procedure using Dynamic SQL to set the WHERE clause. However, one fear of mine is SQL injection from the client. How can I avoid arbitrary SQL injection, yet still allow for a dynamic WHERE clause to be passed into the stored procedure? Jason Pacheco From here http://www.microsoft.com/technet/prodtechnol/sql/2000/maintai...

Stored Prodesure and Dynamic SQL for SQL injections ??
hi friends sqldatasource1.selectcommand="select * from table1 where username=@username" sqldatasource1.selectparamaters.add=("username", textbox1.text) sqldatasource1 from data of toolbox and it connects gridview1.. Should i use stored prosedure for security ? or my code is enough ? my second question is.. if i dont use textbox (i mean user cant enter a value for username), should i use paramaters ? or can i use only "select * from table1 where username='John'"Mark as me if my question or my answer can be helpful for you :) Hey, Using comman...

How does exposed Dynamic T-SQL allow a SQL injection attack?
I am reading an article on stored procedures that create other stored procedures. It is discussing dynamic T-SQL used with EXECUTE to generate a stored proc. Then there is this warning (excerped below). I don't quite understand how dynamic T-SQL would be used to cause a SQL injection attack. I am -not- looking for direction to create an attack; rather what exactly should I avoid doing? In the example below, how is dynamic T-SQL different from any other proc where variables are introduced. Thanks in advance, Glenn ----- excerpt ----- Let's start by examining a simple e...

Dynamic SQL in Transact SQL
Does Dynamic SQL can be done in transact sql format in stored procedure? If yes, any reference can be read? Thx..... ...

Dynamic SQL in Sybase SQL
Im trying to create a bit of Dynamic SQL in a Sybase stored procedure, im used to MS SQL Server so im not sure what im doing wrong in Sybase. Heres my bit of code declare @sql varchar(100) declare @sql2 varchar(100) Select @sql = '''0001/100212'',''0001/100218'',''0001/100240'','0001/100243''' select @sql2 = 'select * from table where table_tra_code in (' + @sql + ')' Exec @sql2 now i would have thought this would have executed @sql2 and returned a result set, but no :(. Any ideas?, id...

Converting a gridView using an Sql Datasource to a gridview who is not using an SQL DataSource
How can i rewrite this page so that i doesnt make use of an SqlDataSource?  <%@ Control Language="C#" AutoEventWireup="true" CodeFile="LoginHistory.ascx.cs" Inherits="Private_UserControls_WebUserControl" %> <html> <head> <%--<link rel="stylesheet" href="BaldwinPortal.css" mce_href="BaldwinPortal.css" type="text/css" />--%> <title>Login History</title> </head> <body style="margin:0 0 0 0;padding:0 0 0 0"> ...

Dynamic SQL in Sybase SQL Server
Hey all, Im trying to create a bit of Dynamic SQL in a Sybase stored procedure, im used to MS SQL Server so im not sure what im doing wrong in Sybase. Heres my bit of code declare @sql varchar(100) declare @sql2 varchar(100) Select @sql = """0001/100212"",""0001/100218"",""0001/100240"",""0001/100243""" select @sql2 = 'select * from tablename where table_code in (' + @sql + ')' Exec @sql2 now i would have thought this would have executed @sql2 and returned a res...

Is Dynamic SQL Possible in T/SQL
I come from a Microsoft SQL Server environment, so I am used to being able to create dynamic SQL statements within a stored procedure with the EXEC command. It appears that Sybase's equivalent command only recognizes othere stored procedures. Does anyone know of a way to do this in Sybase? ASE 12.0 supports dynamic execution of strings, see "execute" in Ref Manual. In versions ASE 11.5 and higher, you can also use the sp_remotesql against the "local" server, for much the same effect, although there are more limits using this than the "execute imm...

Dynamic SQL
We have a problem with one cursor, in Embedded SQL/C that uses DYNAMIC SQL. The cursor works fine in SQL Advantage, and when NOT using the ? (dynamic SQL). Other similar dynamic sql cursors work fine also. This cursor always returns SQL NOT FOUND - even though it should be returning multiple rows. We have SQLSERVER 11.0.x - UNIXWARE And we have Adaptive Server 11.5 - NT Same results. Please help. Todd.C.Hampson@boeing.com Could you post your cursor code (declare, open, fetch, while loop) along with index structures of your cursor's table. Not...

SQL function using dynamic sql.
 Hi all I am trying to pass a column name as a variable in a function but it will not let me.  I am sure it is something stupid. I know that you cannot pass a column in a normal select statement as a variable. You have to execute it dynamically, however you cannot use the exec in a function. Please see code belowALTER FUNCTION dbo.GetData(@ColumnName varchar(50))RETURNS Decimal(18,4)ASBEGINDECLARE @Value Decimal(18,4)SET @Value = (SELECT + @ColumnName + FROM Policy   WHERE Grade = 'Revised') -- Tried this way, does not work need to use ExecSET @Value = Exec(&...

Linq to SQL. sproc or dynamic sql?
Im of the old school belif that all data should come from a DB via sprocs for many reasons that i wont go into here. Im getting into website creation and want to call data from my db via linq to sql. I will want to have some form of pagination. is this easily possible using sprocs( without modifying the sprocs) or are you better just use dynamic sql and skip and take keywords? Hi, Please refer to the following link below for the information about "LINQ to SQL (Retrieving Data Using Stored Procedures)" http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrie...

SQL DataSource : Object Reference not set to an instance of an object
 Dear friendsI am getting this error from few days (it was not in past)when i add new "sqlDataSource" , I set the Server name, user name and password then i select the Database, when i click on "Test Connection" button, it does not give me any Error and it shows that Test connection Successful, but when i click "OK", It shows the Error box with the error "Object Reference not set to an instance of an object"From few days it is happening, I was seting the datasource without any problem in past.I am using SQL Server 2005Any one can help? aliusmankha...

Dynamic SQL Format 2 SQL statement
Hi , I tried to implement Dynamic SQL Format 2 SQL statement but it is running fine with out error but it is not saving data to data base INT Dept_id_var =3d 156 String Dept_name_var SetNull(Dept_name_var) PREPARE SQLSA FROM "INSERT INTO dept VALUES (?,?) using my_transaction " ; EXECUTE SQLSA USING :Dept_id_var,:Dept_name_var ; [1] Commit using my_transaction ; [2] To test I tried my_transaction.autocommit =3d true my SQLSA satatement =85. my_transaction.autocommit =3d false but in both case I am not able to insert data to database...

Linq to SQL and stored procedure with dynamic sql
I have a stored procedure like create proc test as ..... set @sql = 'select ' + .... + ' from ....' exec(@sql)The select list is dynamically (in fact, pivot table). However, after drag the stored procedure to dbml design view and called, the stored procedure only return an integer.How to get the result set of the stored procedure when using dbml?Thanks,  Is this the only SELECT statement that @sql gets?  I created a simple example and it performed as I would expect returning a complete recordset....I'm wondering if your stored proc is returning 2 record se...

Web resources about - SQL Injection and Dynamic SQL - asp.net.object-datasource

Talk:Fuel injection - Wikipedia, the free encyclopedia
This article is within the scope of WikiProject Automobiles , a collaborative effort to improve the coverage of automobiles on Wikipedia. If ...

BoSox Injection (@BoSoxInjection) on Twitter
Sign in Sign up To bring you Twitter, we and our partners use cookies on our and other websites. Cookies help personalize Twitter content, tailor ...

Yuki Panda - Injection Game Free on the App Store on iTunes
Get Yuki Panda - Injection Game Free on the App Store. See screenshots and ratings, and read customer reviews.

injections [616] - Flickr - Photo Sharing!
... (In its defense, it was almost six years old, so it's done pretty well.) As a result though, I've temporarily gone back to regular injections. ...

the quack who injected people with fix-a-flat speaks/ man dies from penile silicone injections - YouTube ...
in this video i'm talking about, about the rise of people dying form illegal silicon injections.

Anti-vaxxers flood federal inquiry with complaints about compulsory injections
&nbsp;Thousands of people objecting to the compulsory vaccination of their children are bombarding a federal inquiry into the plan and could ...

Kasia Rivera Gets 5 Years For Causing Man’s Death With Penis Injection « CBS New York
An East Orange, New Jersey woman was sentenced to five years in prison Monday, after admitting to causing the death of a man by injecting silicone ...

Kylie Jenner may regret her lip injections & has ‘been into small lips lately’
Over the past few years, Kylie Jenner’s drastic transformation chiefly revolved around her inflated lips, which she tried to say was all down ...

Ohio delays executions until 2017 due to problems obtaining drugs necessary for lethal injections
Difficulties in obtaining the needed drugs for lethal injection has forced several states, including Ohio, to delay executions. Ohio announced ...

Feds confiscate lethal-injection drugs imported by 2 states
Feds confiscate lethal-injection drugs imported by 2 states

Resources last updated: 11/22/2015 5:03:16 PM