Avoiding SQL Injection With Dynamic SQL
Aug 5, 2004
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 2 Replies
ADVERTISEMENT
Feb 6, 2005
Hi
What is a dynamic is SQL? can u give me a example.
What is a SQL Injection? can u give me a example.
Thinks in advance.
popskie
View 1 Replies
View Related
Mar 12, 2008
Im reviewing my stored procedures for a new application and got to thinking about protecting against sql injection. I think im pretty safe since im using stored procedures and none of them use any 'exec' commands within them, but im not sure.
I was reading this article, and again all the examples that list a stored procedure, have an 'exec' command somewhere that is the culprit. So, in my case lets say I was doing something like this:
Im generally using regularexpression validation controls on the client side of the application and limiting the max length of the input there as well.
Am I safe, or do I need further input checking within the procedure ?
Code Snippet
CREATE PROCEDURE [dbo].[get_Uploads]
@app varchar(50)
--Init variables
SET @error_number = 0
BEGIN TRY
SELECT [Logid],[Filename],[Label],[UploadDate],[App]
FROM UploadLog au
WHERE [App]=@app
END TRY
BEGIN CATCH
SET @error_number = -2
END CATCH
View 1 Replies
View Related
Apr 17, 2007
Hello
I am running an script and the following sentence throws and error because the DTC service is not running in the Remote Server:
insert into MyLocalTable
execute synonym_MyRemoteProcedure @SomeParameter
Since a transaction is not declared within the script, why is the DTC required?
How can I avoid the usage of the DTC? Is there a way to say "this code is not within a distributed transaction"?
Thanks a lot.
View 1 Replies
View Related
Apr 8, 2008
Here is a simplified example of a problem I am facing.
I have 2 tables: Tasks and Employees.
Tasks:
(Task_ID, Task_Name, Task_Type, Task_Requirement, Employee_ID)
Employees:
Emp_ID, Emp_Name, Emp_Specialty, Emp_Task_Cnt, Max_Task_Cnt
Requirements: Write a MS SQLServer 2000 Storeed Procedure to:
1. Update the Tasks table by assigning the task to an Employee.
2. Incrememnt the employee's Emp_Task_Cnt for each Task assigned.
3. Match the Employee to the Task by matching the Task_Requirement to the Emp_Specialty.
4. Do not exceed the employee's Max_Task_Cnt.
I have a working solution to the requirements, but it involves using cursor logic. For all the obvious reasons, I wanted to avoid using a cursor (or cursor-like looping structure) but could not figure out any other way to avoid processing the Task table one record at a time because of the: "4. Do not allow an Employee's Task_Cnt to exeed the Max_Task_Cnt."
Q: Is there a way to do this without using a cursor and still meet all of the requirements?
View 2 Replies
View Related
May 9, 2007
I'm trying to performance tune a procedure and am sort of being thwarted by caching.
When I first run the procedure, it takes a few seconds which is too long in this case. Subsequent executions in Management Studio are nearly instantaneous, though, which I imagine is due to caching and does not reflect the behavior of the procedure in production.
Is there a way to disable caching so that each execution of the procedure in Management Studio will be consistent and reflect the "first run" performance?
View 3 Replies
View Related
Sep 7, 2007
This query uses a cursor to fetch a parameter and pass it to another Stored proc. Is there a straightforward way to do this without using a cursor?
declare @deleteunassigned int
declare cur_unassigned cursor for select distinct a.cust_cont_pk
from cust_cont a, cont_fold_ass b (NOLOCK)
where a.cust_cont_pk != b.CUST_CONT_PK
open cur_unassigned
fetch next from cur_unassigned into @deleteunassigned
while @@fetch_status = 0
begin
exec spDeleteCustContbypk @deleteunassigned
fetch next from cur_unassigned into @deleteunassigned
end
close cur_unassigned
deallocate cur_unassigned
GO
declare @deleteunassigned int
declare cur_unassigned
cursor for
SELECT DISTINCT a.cust_cont_pk
FROM cust_cont a,
cont_fold_ass b (NOLOCK)
WHERE a.cust_cont_pk != b.CUST_CONT_PK
open cur_unassigned
FETCH NEXT FROM cur_unassigned INTO @deleteunassigned
while @@fetch_status = 0
begin
exec spDeleteCustContbypk @deleteunassigned
FETCH NEXT FROM cur_unassigned INTO @deleteunassigned
end
close cur_unassigned
deallocate cur_unassigned
GO
Future guru in the making.
View 2 Replies
View Related
Jul 20, 2005
Using small stored procs or sp_executesql dramatically reduces the number ofrecompiles and increases the reuse of execution plans. This is evident fromboth the usecount in syscacheobjects, perfmon, and profiler. However I'm ata loss to determine what causes a compilation. Under rare circumstances theusecount for Compiled Plan does not increase as statements are run. Seemsto correspond to when there is no execution plan. It would seem to me thatcompilation is a resource intensive task that if possible (data and schemaare not changing) should be held to a minimum.How does one encourage the reuse of compile plans?Is this the same as minimizing compilation?Looks like some of this behavior is changing in SQL 2005....Thanks,Danny
View 3 Replies
View Related
May 4, 2006
I have a stored procedure spUpdateClient, which takes as params a number of properties of a client application that wants to register its existence with the database. The sp just needs to add a new row or update an existing row with this data.
I tried to accomplish this with code somethign like this. (The table I'm updating is called Client, and its primary key is ClientId, which is a value passed into the sp from the client.)
IF (SELECT COUNT(ClientId) FROM Clients WHERE ClientId=@ClientId) = 0
BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@ClientId, @Hostname, @Etc)
END
ELSE
BEGIN
-- client was found, update it
UPDATE Clients
SET Hostname=@Hostname, Etc=@Etc
WHERE ClientId=@ClientId
END
But the client apps call this every second or so, so soon enough I started getting primary key violations. It looks like one client would make two calls nearly at the same time, both would get a 0 value on the SELECT line, so both would try to insert a new row with the same ClientId. No good.
So then I added
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
at the top, and a COMMIT at the bottom. I thought the first one in would get to run the whole sp, and the next one in would have to wait for the first to be done.
Instead I'm now getting deadlock errors.
If I understand the docs right, that's because the exclusive lock is not placed on the Clients table until the INSERT happens, not at the SELECT. So when two calls to the sp happen at nearly the same time (call them A and B), A does the SELECT and that locks Clients so nobody else can update it. Then B does the SELECT, locking Clients so nobody else (including A) can update it. Now A needs to exclusively lock Clients to do its INSERT, but B still has that read lock on it, and they're deadlocked.
I could catch the deadlock in my client app after SQL Server kills one of the transactions, but it seems to me there should be some way to set a lock at the top of the sp that says "nobody else can enter this sp until I exit it". Any such thing?
Thanks.
Nate Hekman
View 9 Replies
View Related
Sep 7, 2007
Hello.
I have been developing a small site that has two backend SQL Server databases. One for my application data and one for the ASPNETDB database that is created by the ASP .NET Configuration utility.
Is it possible to configure the ASP .NET Configuration tool to use my custom database instead of creating a second database called ASPNETDB?
Thanks in advance.
Kev
View 2 Replies
View Related
Jun 9, 2008
Hello all,
I currently have an asp script that is generating a 12 month rolling report. From asp I'm running a for loop with 12 iterations, each one sending the following query:
select count(a.aReportDate) as ttl from findings f left outer join audits a on a.aID = f.auditID
where f.findingInvalid <> 1 and month(aReportDate) = " & Mo & " and year(aReportDate) = " & Yr
where the Mo and Yr variables are incremented accordingly.
I actually have 4 sets of data being pulled back to populate a graph, so this results in 48 queries with each page load! Obviously not ideal. So I'm hoping to reduce this to 4 queries. I was playing with the following in enterprise manager:
DECLARE @DT DATETIME
DECLARE @CNT INT
SET @DT = '10/31/07'
SET @CNT = 1
WHILE(@CNT < 12)
BEGIN
select count(a.aReportDate) as ttl from findings f left outer join audits a on a.aID = f.auditID
where f.findingInvalid <> 1 and month(aReportDate) = month(@DT) and year(aReportDate) = year(@DT)
SET @CNT = @CNT + 1
END
I haven't yet added any logic to increment the date, but my concern is that it looks like it is returning 12 separate results. Is there any way to combine this all into one resultset that will be passed back to my asp script? Hopefully this makes sense?
Suggestions on a completely different approach would also be welcome.
Thanks!
View 2 Replies
View Related
Mar 2, 2007
Hi,
Hope someone could help me in revising a long running query. Here is the query
select *
from table1
where classid is null
and productid not in (
select productid
from table1
where classid = 67)
In here table1 could have several occurance of productid in which productid could have different classid. The possible values of classid are: NULL,1,2,3,67. Basically I am looking for all records whose classid is null but should never had an instance in table1 where its classid is 67.
Do you have something like a "join" statment that will only include all records in the left table that is not in the right table?
Hope someone could help me with this. Thanks in advance.
-Ruel
View 9 Replies
View Related
Oct 8, 2007
MS SQL Server 2005
I have a table in our system that hold temporary data for doing calculations. It will process several million records in it. each time they forecast our products.....
Is there any way to have the SQL server NOT add these transactions to the transaction log, since I'm going to wipe the data anyway? I'd like to be able to pick and choose the tables that are 'backed up' into the transaction log...
Please advice. Thanks
View 8 Replies
View Related
Mar 18, 2008
I am trying to figure out an efficient way of comparing two tables of identical structure and primary keys only I want to do a join where one of the tables reveals values for records which have been modified and/or updated.
To illustrate, I have two tables in the generic form:
id-dt-val
For which the 'val' in table 2 could be different from the 'val' in table 1 - for a given id-dt coupling that are identical in both tables.
Does anyone know of an efficient way I could return all id-dt couplings in table 2 which have values that are different from those with the same id-dt couplings in table 1?
NOTE: I am asking this because I am trying to avoid explicit comparisons between the 'val' columns. The tables I am working with in actuality have roughly 900 or so columns, so I don't want this kind of a monster query to do (otherwise, I would simply do something like where a.id = b.id and a.dt = b.dt and a.val <> b.val) - but this won't do in this case.
As a sample query, I have the following script below. When I attempt the where not exists, as you might expect, I only get the one record in which the id-dt coupling is different from those in table 1, but I'm not sure how to return the other records where the id-dt coupling is the same in table 1 but for where modified values exist:
create table #tab1
(
id varchar(3),
dt datetime,
val float
)
go
create table #tab2
(
id varchar(3),
dt datetime,
val float
)
go
insert into #tab1
values
('ABC','01/31/1990',5.436)
go
insert into #tab1
values
('DEF','01/31/1990',4.427)
go
insert into #tab1
values
('GHI','01/31/1990',7.724)
go
insert into #tab2
values
('XYZ','01/31/1990',3.333)
go
insert into #tab2
values
('DEF','01/31/1990',11.111)
go
insert into #tab2
values
('GHI','01/31/1990',12.112)
go
select a.* from #tab2 a --Trouble is, this only returns the XYZ record
where not exists
(select b.* from #tab1 b where a.id = b.id and a.dt = b.dt)
go
drop table #tab1
drop table #tab2
go
I really dont' want to have to code up a loop to do the value by value comparison for inequality, so if anyone knows of an efficient set-based way of doing this, I would really appreciate it.
Any advice appreciated!
-KS
View 7 Replies
View Related
Jul 20, 2005
The C++ application calls the database to look up property data. Onetroublesome query is a function that returns a table, finding data whichis assembled from four or five tables through a view that has a join,and then updating the resulting @table from some other tables. Thereare several queries inside the function, which are selected accordingto which parameters are supplied (house #, street, zip, or perhaps parcelnumber, or house #, street, town, city,...etc.). If a lot of parametersare provided, and the property is not in the database, then several queriesmay be attempted -- it keeps going until it runs out of queries or findssomething. Usually it takes ~1-2 sec for a hit, but maybe a minute insome failure cases, depending on the distribution of data. (~100 milproperties in the DB) Some queires operate on the assumption the input datais slightly faulty, and take relatively a long time, e.g., if WHEREZIP=@Zip fails, we try WHERE ZIP LIKE substring(@Zip,1,3)+'%'. Whileall this is going on the application may decide the DB is never going toreturn, and time out; it also seems more likely to throw an exception thelonger it has to wait. Is there a way to cause the DB function to fail ifit takes more than a certain amount of time? I could also recast it asa procedure, and check the time consumed after every query, and abandonthe search if a certain amount of time has elapsed.Thanks in advance,Jim Geissman
View 3 Replies
View Related
Jun 8, 2007
What is the experession to evaluate if the result of a computation would be a divide by zero error for a text box in report?
IIF(divide by zero, display nothing, else display computed result)...??
View 6 Replies
View Related
Dec 4, 2007
I have a Master/Detail table setup - let's call the master "Account" and the detail "Amount". I also have a "black box" stored procedure (BlackBox_sp) which carries out a lot of complex processing.
What I need to do is, for each Account, I need to iterate thtough it's Amount records and call the black box each time. Once I've finished going through all the Amount records, I need to call the black box again once for the Account. This must be done with the Account & Amount rows in a specific order.
So I have something along the lines of
Code Block
DECLARE Total int
DECLARE Account_cur
OPEN Account_cur
FETCH NEXT FROM Account_cur
WHILE FETCH_STATUS = 0
BEGIN
SET Total = 0
DECLARE Amount_cur
OPEN Amount_cur
FETCH NEXT FROM Amount_cur
WHILE FETCH_STATUS = 0
BEGIN
SET Total = Total + Amount
EXEC BlackBox_sp (Amount)
END
CLOSE Amount_cur
EXEC BlackBox_sp (Total)
END
CLOSE Account_cur
Any tips on another approach would be appreciated given the contraints I have.
Greg.
View 1 Replies
View Related
Mar 27, 2008
I have web site when people orders through website at same time, a problem can be arrive when allocating next primary key value to new record, using maximum number of records +1
how to avoid this problem and insert to sql server
please give me your ideas
View 16 Replies
View Related
Mar 7, 2001
Hi there,
I'm using a query to fetch data from a table where one of the criteria is IN(...) clause for the key column of the table.Now the data being retrieved is ordered by the key column of the table even though I haven't specified any order by clause.
I want to know if there a way in which the data being fetched is in the order of my IN(...) clause.
Thanx
Aby
View 3 Replies
View Related
Feb 13, 2002
Hi there!
Here is my situation:
table 'ReceiptHeader'
IDCustomerIDDateCreated
1225102/06/2002
1332102/09/2002
1444002/15/2002
table 'ReceiptDiscount'
IDDiscountIDReceiptHeaderIDAmount
111210.00
241250.00
311325.00
**a receipt can have multiple discounts**
Table 'ReceiptDetail'
ReceiptHeaderID LineItemIDTotal
121155.33
131145.33
141241.66
**for this example there is only one line item per receipt**
Without using a cursor, i would like to return a result set
like this one below using a set based solution...
ReceiptIDCustomerIDDiscountTotal
1225160.00155.33
1332125.00145.33
144400.00241.66
Thanks,
SF
View 1 Replies
View Related
Jun 26, 2002
Hi,
Can anybody help me how to Stop this 'System Creation Indexes' (Index Name like 'WA%')?.
Is there any method is available to delete the Existing 'System Indexes'?.
thanks,
Srini
View 1 Replies
View Related
Mar 9, 2007
Guys,
Is there a way to temporarily disable logging into the transaction log.
In our system, we perform purging of our database every night, where the purging consists of 2 steps:
1. For each table, insert the data, to be deleted, into a corresponding "purged" table, to remain there for one day only.
2. For each table, delete the unnecessary data (i.e. same data stored in purged tables in step 1)
During these 2 steps, the transaction log grows, and since we perform the transactional log back up, the back up at that time is huge. We are running a bit low on the hard disk space and I'd like to disable logging into the transaction log when these operations are performed.
I really don't care about being able to recover this data.
I thought that one option is to set the database to simple recovery, then perform the purging of the database, and then change back to full.
However, I think that trans log can grow even if recovery model is simple [although you won't be able to retrieve any changes].
So, is there a way to delete a portion of a table [or insert into it] so that no data is written to a transaction log (I know that we can use TRUNCATE if we need to remove whole table without logging)?
Thanks a lot
View 13 Replies
View Related
Nov 15, 1999
Help,
Have a user who needs many data changes to a huge table with over 2 million rows.
He hopes to do this by running a series of queries.
Last time our log grew gargantuan. I know 7.0 is wonderful & dynamic, but after
an hour of rambling through books online I'm more confused than ever.
Is there a way we can code into his query at intervals any hard coded checkpoints
or log backups? Do we need to dbcc shrinkfile the logfile?
Thanks,
Mark Blackburn
mark@mbari.org
View 1 Replies
View Related
Sep 6, 2006
Hi
I currently have two tables called Book and JournalPaper, both of which have a column called Publisher. Currently the data in the Publisher column is the Publisher name that is entered straight into either table and has been duplicated in many cases. To tidy this up I have created a new table called Publisher where each entry will have a unique ID.
I now want to remove the Publisher columns from Book and JournalPaper, replace it with an ID foreign key column and move the Publisher name data into the Publisher table. Is there a way I can do this without duplicating the data as some publishers appear several times on both tables?
Any help with this will be greatly appreciated as my limited SQL is not up to this particular challenge!!!
Thanks!
View 7 Replies
View Related
Oct 31, 2007
Hello -- I have a Huge Table Tab1 (160 Mill Rows). I have a script that Inserts and Deletes from this Tab1 (This Script runs for a good 4/5 Hrs).
When the script is being executed, No other session can even do a "SELECT TOP 10 * FROM Tab1"
How do i avoid this? Any "NOLOCK" Keywords i should be specifying in my Script?
Thanks in advance
View 3 Replies
View Related
Jun 23, 2015
I have a function with multiple if ( condition) which is CPU intensive. How could I avoid this.
CREATE FUNCTION prici.[fn_pricipalamt]
(
-- Add the parameters for the function here
@Tcode char(10),
@SecTypeCode1 char(10),
@SecTypeCode2 char(10),
@TradeAmount float,
[Code] .....
View 2 Replies
View Related
Oct 26, 2006
Hi!
I have an SQL Server where only a group of sysadmins have access to install DTSX packages. Those DTSX packages are developed by another team that does not have access to the production SQL Server. They use their own SQL Server.
In order to make it as simple as possible to install these packages by the sysadmins, I suggested the use of configuration files. The files are associated with the job that executes the package and all that has to be done to install the package is copy it to the file system or import it into the SQL Server. Developers use their configuration file, sysadmins user theirs. Nothing new here.
The problem is that some of the packages have to access some old systems and we cannot use integrated authentication. We have to use SQL authentication and therefore specify a user account and password in the connection string. If this is stored in the configuration file, it is available in clear text! If I store the configuration in the package itself using ProtectSensitiveWithPassword protection level, the sysadmins will have to edit every DTSX package to reset the connections to the production environment (the developers always send them with their development configurations) and I don't want that. If I store it in a SQL Server database, it seems the sysadmins also have to edit the package to point the package configuration to the correct database and set the configuration filter.
Another solution is to store the credentials in clear text in the configuration file but set the file system permissions on that file so only the account that executes the package can read them (this is what I'm implementing if nothing better comes up...)
Is there any other way to do this? Am I doing something wrong?
Thanks in advance.
View 5 Replies
View Related
Apr 16, 2014
What I am trying to do: Obtain attendance percentages for schools for the last five days. The outcome would look like this:
DISTRICTGROUPING, SCHOOLNAME, 5 DAYS AGO PCTG, 4 DAYS AGO PCTG, 3 DAYS AGO PCTG, 2 DAYS AGO PCTG, 1 DAY AGO PCTG
I am using nested subqueries for each day as follows: (total enrollment-total absent/total enrollment)
,(
((SELECTCOUNT(*)--GET TOTAL ENROLLMENT COUNT FOR SPECIFIED DATE
[Code]....
The query works with the following exceptions:
My issues are:
1. Avoid the "division by zero" error. This can occur if a school is closed for a day or if a smaller school has no absences for a day.
2. Avoid weekend dates. I need the query to display only weekdays
3. Currently I am using "PERCENTAGE 5: as a column header whereas I need the actual date as the header.
View 6 Replies
View Related
Sep 26, 2014
Using a string of IDs passed into a stored procedure as a VARCHAR parameter ('1,2,100,1020,') in an IN without parsing the list to a temp table or table variable. Here's the situation, I've got a stored procedure that is called all the time. It's working with some larger tables (100+ Million rows). The procedure passes in as one of the variables a list of IDs for the large table. This list can have anywhere from 1 to ~100 IDs passed to it.
Currently, we are using a function to parse the list of IDs into a temp table then joining the temp table to get the query:
CREATE PROCEDURE [dbo].[GetStuff] (
@IdList varchar(max)
)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
[Code] .....
The problem we're running into is that since this proc gets called so often, we sometimes run into tempDB contention that slows this down. In my testing (unfortunately I don't have a good way of generating a production load) swapping the #table for an @table didn't make any difference which makes sense to me given that they are both allocated in the tempDB. One approach that I tried was that since the SELECT query is pretty simple, I moved it to dynamic SQL:
CREATE PROCEDURE [dbo].[GetStuff] (
@IdList varchar(max)
)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
[Code] ....
The problem I had there, is that it creates an Ad Hoc plan for the query and only reuses it if the same list of parameters are passed in, so I get a higher CPU cost because it compiles a plan and it also causes the plan cache to bloat since the parameter list is almost always different. Is there an approach that I haven't considered that may get the best of both worlds, avoiding or minimizing tempDB contention but also not having to compile a new plan every time the proc is run?
View 9 Replies
View Related
Jul 4, 2006
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 2 Replies
View Related
Nov 25, 2007
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 6 Replies
View Related
Jun 5, 2008
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 4 Replies
View Related
Jun 16, 2005
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 11 Replies
View Related
