My site has come under attack from sql injections. I thought I had
things handled by replacing all single quotes with two single quotes,
aka
Replace(inputString, "'", "''")
Alas, clever hackers have still managed to find a way to drop columns
from some of my tables. Can anybody direct me towards a best practice
document on preventing these 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
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?
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?
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?
For entertainment purposes, I've been reading some articles on SQL Injection Attacks and there should be a cover charge to read these articles. (excuse the sarcasm)
Most defense is based on the use of stored procedures or read only settings on the tables.
I'm looking for practical opinions and possibly some code that would convince me personally that this is indeed a real threat.
Also, if possible, please post remedies that are solid and not open ended.
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
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
I use paramaterized queries when using ad-hoc queries in my code-behind. Everywhere website I visit says to use stored procedures or paramaterized queries if stored procedures cannot be used. I understand how SQL injection attacks work, but could someone please enlighten me why a paramaterized query helps prevent these attacks? It seems to me that the text that is entered on the web form would just be passed into the paramater, but I'm obviously missing something. Thanks.
Dear guys I'm developing a web site that subscribers will directly connect to sql express and send results of their desktop application to database. So every body, including hackers can easily obtain a login. ofcourse I will restrict each login to execute only one stored procedure specially created for himself and do nothing else. (or anotherquery to verify validity of each user, instead of creating special stored procedures for each one) But, I'm worried about frequency of running that stored procedure buy bad users. I can limit frequency of running stored procedure, but anyhow each time of such control also takes time. should I pay attention to such matter?
I also don't know what if a user tries to broute force server with incorrect user names and passwords repeatedly. does it lead to slowing down the server? again, should I pay attention to such matter?
does firewall help? does IP tarcking help? are these enough? if so, where can I find extra information?
In fact this problem persists even if I want to redirect user communications to web service instead of direct connection to data base or even using emails. Anyhow hackers can do the same thing but only some more complexity is added to my project. I found this article for IP tracking in web sites http://weblogs.asp.net/omarzabir/archive/2007/10/16/prevent-denial-of-service-dos-attacks-in-your-web-application.aspx . Is this enough?
I wish to know the way big networks do in real world. Can any one please help me? Infact I'm not a professional in security. what ever kind of extra suggestions will be appreciated.
I am running MS SQL 2005 Express I get per day 2-4 hackers attacks trying to login from €œsa€? Some 37 calls times per second one of attack was continuing 4 days
Is there some setting into MS SQL 2005 to reduce that?
Can you recommend me good firewall for DDOS attacks?
Is it there some legal action that I can take to this people I have their IPs most are from US and Canada?
Hi, I´m new on this forum, I just need help to solve or avoid better saying attacks to a SQL Server 2005 database. I ´ve had some intruders on my database, changing some data on 2 tables. the information there is too important. But i need to know how can i get all the remote address that make some masive updates on my DB. I´ve make some triggers to avoid that, but those peaoples have reach modify data. I think is some user on the same VPN.
Help me please what can i do to get the ip address.
It is not uncommon for me to review the event logs of our SQL Server and notice that someone is attempting to figure out the password for one of the SQL user accounts.........especially the "sa" username. But lately our SQL server has to be rebooted to where it is starting to become a nightly thing.
Last night the SQL Server was bombarded with attempted failed connections for 2 hours before it finally gave a blank BSOD. The SQL Server in question is in mixed mode and is a shared server. Strictly using Windows Authentication mode is not an option for us. The server has "beefy" hardware and has all updates and patches.
Sometimes, when I log on the server and notice that an IP address is making such attacks on the server, I put up an IPSEC policy against that IP. But that is not a good solution for reasons I dont think need to be outlined here.
So my question is, what can I do to better protect our SQL Server from these types of attacks? My thought on these attacks is not different than a DDOS that eventually takes the server down.
I have already done TCP/IP Hardening but not sure what else to do.
1 ALTER PROCEDURE links_GetAllLinks 2 @startRowIndex INT, 3 @maximumRows INT, 4 @sortExpression nvarchar(50) 5 AS 6 7 8 9 10 11 SET @startRowIndex = @startRowIndex + 1 12 --SELECT @TopicsFrom = CASE @TopicsDays WHEN '1' THEN DATEADD(day,-1,getdate()) WHEN '2' THEN DATEADD(day,-7,getdate()) WHEN '3' THEN DATEADD(day,-14,getdate()) WHEN '4' THEN DATEADD(month,-1,getdate()) WHEN '5' THEN DATEADD(month,-3,getdate()) WHEN '6' THEN DATEADD(month,-6,getdate()) WHEN '7' THEN DATEADD(year,-1,getdate()) ELSE DATEADD(year,-1,getdate()) END 13 -- populate the table CAST(getdate() as int) 14 15 16 IF @sortExpression LIKE '' 17 BEGIN 18 SET @sortExpression = 'links_Links.link_date' 19 END 20 DECLARE @sql nvarchar(4000) 21 SET @sql = 'Declare @Links TABLE 22 (RowNumber INT, 23 link_id INT, 24 cat_id INT, 25 cat_title VARCHAR(100), 26 sub_id INT, 27 sub_name VARCHAR(100), 28 link_aproved BIT, 29 link_name VARCHAR(100), 30 link_url varchar(100), 31 link_desc varchar(500), 32 link_date datetime, 33 link_rating INT, 34 link_ratingscount INT, 35 link_ratingvalue INT, 36 link_poster uniqueidentifier, 37 link_comments INT, 38 hit_date datetime);INSERT INTO @Links 39 SELECT ROW_NUMBER() OVER (ORDER BY ' + @sortExpression + '), links_Links.link_id, links_Links.cat_id, links_Categories.cat_title, links_Links.sub_id, links_SubCategories.sub_name, links_Links.link_aproved, links_Links.link_name, links_Links.link_url, links_Links.link_desc, links_Links.link_date, links_Links.link_rating, links_Links.link_ratingscount, (links_Links.link_rating/links_Links.link_ratingscount) as link_ratingvalue, links_Links.link_poster, links_Links.link_comments, links_Links.hit_date 40 FROM links_Links INNER JOIN 41 links_Categories ON links_Categories.cat_id = links_Links.cat_id INNER JOIN 42 links_SubCategories ON links_SubCategories.sub_id = links_Links.sub_id 43 WHERE links_Links.link_aproved = 1;SELECT * from @Links 44 WHERE RowNumber BETWEEN ' + CONVERT(nvarchar(10), @startRowIndex) + ' AND (' + CONVERT(nvarchar(10), @startRowIndex) + ' + ' + CONVERT(nvarchar(10), @maximumRows) + ') - 1' 45 46 47 48 EXEC sp_executesql @sql 49 50 51 RETURN at row 39, i define a field by dividing to values. I use these for ratings (finding the mean). The problem is, when a new link is submited, it has no ratings, so it performs a divide by zero. how do I check if the column "link_ratingscount" is zero? if it is, then return a zero, and if it is not zero, then do the divide?
My problem is that the 'INSERT INTO' query that sends the records to thetable is dynamically compiled in VBA and and the target table has a twocolumn primary key. I have made a number of attempts at getting 'WHERENOT EXISTS' to cure the problem but so far without success and previouspostings have resulted in advice to create an 'ignore duplicates' index.This solved the problem in asmuch as it allowed the SQL to insert the records that did not alreadyexist but resulted in the message appearing every time the user ran thethe query. Whilst this is not a major problem it is vaguely irritatingand I would like to find a way to stop it happening. I suspect that thesolution may involve using the @@ERROR command but I am not sure of thesyntax.RegardsColin*** Sent via Developersdex http://www.developersdex.com ***
Hello Everyone, I have a web form that allows the user to select a time to reserve. I am trying to make it so that when a user selects a date to schedule something (which i have working) a drop down list will fill with times that have not been reserved. The data is stored in two tables: tblCalendar and tblTime. tblTime contains pkTimeID and times (which are all possible times to select from in half hour intervals). tblCalendar contains a few fields but timeID and date (which is in the format M/d/yyyy) are what I need to test against. timeID is the foreign key of pkTimeID. Basically when the user selects the date, a function gets called that will run a SELECT statement to get the times available. All of this works, I am able to fill the ddl with all times available no matter what the date is or what has already been reserved. I want to check if a time has been already selected based on the date selected by the user and only allow times not selected to be listed. After acheiving this I would like to prevent the immediate time before and immediate time after from being displayed because each reserved time will last for one hour but the data is stored in half hour increments. Any help/suggestions/links will be greatly appreciated. If I need to provide anything else please let me know. Thanks in advance, Brian
This morning I found that during the night someone hacked into my remote hosted SQL server 2005 database. Every record in a description field in each of three tables had everything after the first sentence replaced with the same URL. The three pages that used these tables badly infected anyone who viewed them in IE.I had my host restore the 12/30 backup, and I changed all the database and ftp passwords, now I want to be sure my code isn't giving anyone access. I'm using strongly typed datasets. This is my code behind code.Dim sCategory = Request.QueryString("cat")Dim ResourceAdapter As New ResourcesTableAdapters.ResourcesTableAdapterDim dF As Data.DataTable = ResourceAdapter.GetDataByCategory(sCategory)Dim numRows = dF.Rows.CountLabelCat.Text = sCategoryRepeater1.DataSource = dFRepeater1.DataBind() Is there anything I can do to better secure this? Oh, my connect string is in the web.config file only, but it isn't encrypted. I will be encrypting it today.Diane
Hi all.. I've been scouring the forums for about 6 hours to no avail. This is a really simple question. I'm trying to have a registration page that lets the user input name, email, desired username, and password. I want to check the username and email fields to make sure ppl cannot sign up twice. So from what I've gathered I have a couple of options:
1) i can set up a unique constraint on the database columns, 2) i can run a select statement before inserting, 3) i can store the whole database column in a variable then search through it.
My question is how to do option 2? All of my transactions are through a sqldatasource object in c#.
Hi I have a problem. Every time I select the first Item of a dropdownlist, it prevents the Insert to the database. There are 8 droplists on the page but the insert is only effected by the First Item of lstTheme selcetion. My Insert code is here: Any ideas?? Private Sub btnInsertChange_Click(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.CommandEventArgs) Handles btnInsertChange.Command
I have a schema/user called mstp who has tables in another schema/user. How do I prevent the mstp user from seeing those tables/data that do not belong to their schema?
example:
database xxxx has tables som.employees and mstp.employees The mstp user should only allowed access to mstp.employees and nothing else.
I'm using MSSQL7, NT authentication and application roles so only my application can access the data. Also, other applications (like Excel) can not access the data and read it. So far, so good...
Yet, I noticed that if I try to access the SQL Server from another SQL Server on the network, it is allowed to see the list of tables, SP, etc. It is not allowed to open the table, but the Import/Export wizard is working and will allow retrieving data from the secured tables. If I change to MSSQL authentication, any user will be able to access the data from my application and I don't want that either.
Unless I'm missing something, this is a big problem, especially today where any VPN connection with valid user name and password can actually log in to the domain and therefore connect to the database via SQL Server.
By the way, the server still must allow access to users via applications so logins must exist. I just don't want other SQL servers on the network to be able to connect to and import/export, view table and SP, etc.
I have a table using an identity column as its Primary Key and twocolumns (table reduced for simplicity) EmployeeNumber and ArrivalTime.CREATE TABLE [tblRecords] ([ID] [bigint] IDENTITY (1, 1) NOT NULL ,[EmployeeNumber] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_ASNOT NULL ,[ArrivalTime] [datetime] NOT NULL ,CONSTRAINT [PK_tblRecords] PRIMARY KEY CLUSTERED([ID]) ON [PRIMARY]) ON [PRIMARY]GOI have an insert procedure that checks for duplicates before insertinga new record:IF (SELECT TOP 1 [ID] FROM tblRecords WHERE EmployeeNumber =@SocialSecurity) IS NULLBEGININSERT INTO tblRecords(EmployeeNumber,ArrivalTime)VALUES (@EmployeeNumber, @ArrivalTime)SELECT SCOPE_IDENTITY()ENDELSESELECT 0 AS DuplicateRecordIn 99.9% of the cases, this works well. However, in the event that theinsert attempts are literally "ticks" apart, the "SELECT TOP 1..."command completes on both attempts before the first attempt completes.So I end up with duplicate entries if the procedure is called multipletimes vey quickly. The system needs to prevent duplicateEmployeeNumbers within the past 45 days so setting the EmployeeNumberto UNIQUE would not work. I can check for older entries (45 days ornewer) very easily, but I do not know how to handle the times when theprocedure is called multiple times within milliseconds. Would aTRANSACTION with a duplicate check after the INSERT with a ROLLBACKwork in this case? Any help is greatly appreciated!-E
The database is configured for single publisher, many subscribers,merge replication. The maintenance plan started to fail a couple ofmonths ago and the database would not get backed up. After clearingall the locks, I am able to backup the database manually. The locksreturn again and I'm not able to backup the database with themaintenance plan. How can I get around the lock issue or solve it sothat I can backup the database again?Thanks,Chris
I'm using MSSQL7, NT authentication and application roles so only my application can access the data. Also, other applications (like Excel) can not access the data and read it. So far, so good...
Yet, I noticed that if I try to access the SQL Server from another SQL Server on the network, it is allowed to see the list of tables, SP, etc. It is not allowed to open the table, but the Import/Export wizard is working and will allow retrieving data from the secured tables. If I change to MSSQL authentication, any user will be able to access the data from my application and I don't want that either.
Unless I'm missing something, this is a big problem, especially today where any VPN connection with valid user name and password can actually log in to the domain and therefore connect to the database via SQL Server.
By the way, the server still must allow access to users via applications so logins must exist. I just don't want other SQL servers on the network to be able to connect to and import/export, view table and SP, etc.
I have a letter I am creating in SSRS and would like the data to flow smoothly from one page to the next even if the whole section doesn't fit on a page instead of it forcing a page break which results in a large blank space on the page before. I have put the data into multiple text boxes (can grow is set for the text boxes) because of formatting requirements (I also tried the table object).
--Start Example--
TEXT HEADER 1 very long report letter information here (call it detail 1)
TEXT HEADER 2 even more very long report letter information here (call it detail 2)
TEXT HEADER 3 The term ""Accountant€™s Letter"" as used herein shall mean the certain math verification report, to be provided by an independent certified public accountant, certifying that the Securities shall generate the debt service payments due under the Loans. (call it detail 3)
--End Example--
So assume that everything but detail 3 fits neatly on the first page. Now part of detail #3 should fit on page one but it pushes the whole section to page 2 because it doesn't all fit. All I want is for say line to to be the last line on page #1 and lines 2 and 3 to be in page 2 instead of lines 1 through 3 on page 2. In reality, this letter is about 6 pages and each section is about a half page to 2 pages. This perceived minor issue has cost me a good day+ set back. Thanks,
I need to be able to prevent an invalid character from being entered into a sql 2000 databae on import from oracle.
In short, I need to exclude a certain character from being entered and need to be able to send an email which specifies that an attempt was made to enter this character, if the change was due to an insert or an update, the row to be affected in the target database, date and time info. Also the source of the data.
If this is not possible, is it viable to remove the character after insert and still send the email withe the required info?
Any one any ideas on the cleanest way to achieve this?
I have a user AD account SOPUSJSmith for example. I have given this account security admin at the SQL Server level and dbo at the database level. Is there any way to prevent them from using ODBC to pull data into an Access DB or an Excel spreadsheet?
I am trying to deploy a SQL Server Express database to a remote SQL Server 2005 host. I was planning to copy and paste the data from my local tables to the remote tables using Management Studio Express but my ISP (NTL) appears to be blocking port 1433 and preventing me from getting a connection to the remote database. Will an upgrade to SQL Server 2005 Developer Edition help me? I understand that it has a Backup & Restore Wizard but how does this work? Does it rely on a remote connection and will it fall foul of the NTL port blocking? Or, can the file that the Backup stage produces be copied into the web-based MSSQL Manager, that my web host provides, to restore to the remote database? Any help would be much appreciated.
Is there a way to use trusted connections in ASP.NET and WinForm applications yet prevent users from accessing databases directly (outside of applications) ? I know the use of trusted connections are recommended for several reasons however I have a lot of applications that I need to prevent users from accessing databases directly outside of the applications themselves.
Hi,i am using SQL server 2005 and have a table with 4 columns.Column1 is primary key,col2 is foreign key and col3 and col4 are regular data column.When the user enters the data i want to make sure that for a given foreign key(col2),entries in col3 are not duplicated.Is there a way,i can make sure this at db level,using some kind of constraints or something?Thanks a bunch..
Hi, i need an opinion on this...to prevent the duplicate record in db,i am using unique constraints for a column or combination of column as the case may be.By reading this article http://aspalliance.com/687_Preventing_Duplicate_Record_Insertion_on_Page_Refresh.5 , i get the feeling that its not such a good idea..i am wondering,what does it imply?Does it mean that unique constraints are not reliable enough?Does it mean,it may break and let the duplicate record inserted,even though its not suppose to?I am using SQL server 2005 I have read Dino's article on dup recs and i have still not understood it completely.. i am looking for some not so complex ,full proof method,to prevent duplicate record insertion by clicking refresh or multiple (careless)clicking on submit....thanks ..