How To Protect From SQL Injection In ASP.NET And SQL 2005 For Custom Query Expression?
Oct 3, 2006
How to Protect From SQL Injection in ASP.NET and SQL 2005 for custom query expression?
In my project, I allow user to custom query expression through UI, such as
string queryCondition=' sale>20 and sale <100'
string queryCondition=' createDate>"10/10/2005"'
string queryCondition='Fullname like "%Paul%" '
.
.
.
I construct SQL based the queryCondition string, such as
string mysql='select * from mytable where '+queryCondition
I know it's very dangerous because of SQL Injection, but it's very convenient for user to custom query expression
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?
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
I am writing a custom task that has some custom properties. I would like to parameterize these properties i.e. read from a varaible, so I can change these variables from a config file during runtime.
I read the documentation and it says if we set the ExpressionType to CPET_NOTIFY, it should work, but it does not seem to work. Not sure if I am missing anything. Can someone please help me?
In the Editor of my custom task, under custom properties section, I expected a button with 3 dots, to click & pop-up so we can specify the expression or at least so it evaluates the variables if we give @[User::VaraibleName]
Panos writes "I am working with SQL Server 2005 Express Edition. How can I protect mdf files from being transfered (attached) and be used to another SQL server in another machine? Thanks Panos"
I have SQL Server 2000 Developer Edition on my laptop which I want to remain for work development purposes (my work uses SQL Server 2000). However, I'm developing a .NET application which requires a database back-end.
Can I install SQL Server 2005 Express Edition alongside SQL Server 2000? Or does 2005 overwrite 2000?
If no to the first question then how do I develop the system/database in SQL Server 2000 but delpoy it to the user using 2005 Express Edition?
Cannot open backup device '{CC60D260-C5DD-406A-9E63-64A9503A9763}1'. Operating system error 0x80770006(error not found).
This is followed by Event ID 3041:
BACKUP failed to complete the command BACKUP DATABASE CommunityServer. Check the backup application log for detailed messages.
It looks to me like the virtual device creation fails in the first step, the next three event messages are the cleanup of the failed virtual device, and the final two messages are the failed SQL backup as the expected device doesn't exist.
My question is why? The message seems to indicate bad memory, but I'm sure the physical memory is good - The 16GB in this server has been tested extensively, and I have no other issues. Perhaps its some sort of memory allocation error?
I'm going to apply cumulative update 7 to this SQL server to see if it makes a change. What's the latestest version of sqlvdi.dll available?
We have a commercial VB.NET winforms client/server application that utilizes SQL Server 2005 express edition. The schema and data that the application utilizes is proprietary and could be very damaging if it got into a competitors hands.
Is there any way to protect the data and schema of a sql server 2005 express edition database?
Is there a way to evaluate an expression (like the derived column component) in a custom component? If so where should I look first? Is there an example?
An extremely simple sample is to put in an expression and evaluate one column and then add that to another column to create a new column. i.e. newcolumn = column1 + column2.
I realize that the derived column allows me to do this but I'm trying to figure out if it is possible to do this in a custom component without having to build my own expression evaluator.
Hi, I have a report that needs sorting on a column that is calculated. however, the calculation is so complex (iterative calculations) i've had to do that within my custom code. I am getting the results back well and good but i cannot perform a sort based on that. My problem is: how do i write a custom sort expression function that will direct the column to sort my table based on say the value(s) returned from that function. I want for example:
Code Snippet Public Function mySortExpression(byVal myIDorWhatever as Something) as CustomSortableExpression
' Perform some calculation ' Return some value(s)/array or anything that the column can appreciate and sort the table accordingly with End Function
then in say the Interactive Sort Sort Expression, i'll have the code:
I'm wondering if anyone's accomplished this before - I've been unable to find a whiff of info on how to do this so far.
I'm creating a custom component that I'd like to give a "Derived Column" type of ability to. By that, I mean I'd like to populate a property of my component with an expression (including references to input columns, package variables and functions) and be able to evaluate it at runtime - per row processed by the component.
I would also appreciate any information as to how to provide the interface to allow the user to build such an expression as well - is there a UI function in SSIS I can call to pop up the "expression builder"?
I would like to add additional string functions and other types of functions to the expression builder in SQL Server Integration Services. Right now the list of functions is relatively limited to such things as FINDSTRING, RIGHT, LEN, etc.
Ive been using SSIS for a month or two and now find I need to create some custom tasks to perform some performance logging. in the the overloaded ProviderComponentProperties section I am trying to create a property which has the same look as the Expressions properties you find elsewhere (Little + on the left and a group of sub properties when expanded).
Ive have played with creating a IDTSCustomPropertyCollection90 collection then adding my sub properties to it but I cant seem to then add my new collection to the ComponentMetaData.CustomPropertiesCollection.
Im assuming the Expressions parameter is a collection added to the properties collection but I cant figure out how. Any help would be much appreciated.
I created a Custom Task which it has a Property called ConfigFilePath. I'm overriding the Validate() method from Task. I want to throw an error if my property ConfigFilePath is empty and if the expression for this property is empty. So far, I can check if the property is empty but I don't see how I can access the Expressions Collection of my Custom Task.
I have implemented a custom source component that can be used as the data source in the Data Flow task.
I have also created a custom UI for this component by using the IDtsComponentUI .
But my component does not have the capability of setting the custom properties via the DTS Variables using the Expression Builder.
I have looked around for samples on how to do this, but I can only find samples of how to do this for custom Control Tasks, i.e. IDtsTaskUI.
My question is, How can implement the Expression Builder in my custom Source component + custom Source UI. Or do you know of any samples which I can look at.
Reading about SQL Injection attacks I came across this example:
SSN="172-32-9999';DROP DATABASE pubs --" SqlQuery = "SELECT au_lname, au_fname FROM authors WHERE au_id = '" + SSN + "'"
One remedy given was a parameterized query as follows:
Dim cmd As new SqlCommand("SELECT au_lname, au_fname FROM authors WHERE au_id = @au_id") Dim param = new SqlParameter("au_id", SqlDbType.VarChar) param.Value = SSN cmd.Parameters.Add(param)
Why does this parameter which is defined as a varchar solve the problem? It's defined as a varchar, basically a string. Why is the result different in the solution? How is the query string in the second sample different from the one in the first? A simple question I know, but I've been wondering.
I am creating a custom transformation component, and a custom user interface for that component.
In my custom UI, I want to show the custom properties, and allow users to edit these properties similar to how the advanced editor shows the properties.
I know in my UI I need to create a "Property Grid". In the properties of this grid, I can select the object I want to display data for, however, the only objects that appear are the objects that I have already created within this UI, and not the actual component object with the custom properties.
How do I go about getting the properties for my transformation component listed in this property grid?
My client maintains its HR data in an application that uses Oracle as its backend. This highly-sensitive data is basically off limits to all but a select few. Presently, I use a program in Access 97 that allows one high level HR person to pass their login to linked Oracle tables and copy a large chunk of this data to Access tables. From there I can morph it as needed for the Personnel, Safety, EEOC and other areas. The client sees this PW-protected, encrypted Access DB as safe because, being "only an Access DB", it falls below the radar of IS. This basically means IS can't get to the data. However, accessibility and scalability are non-existent. I'd like to reduce the Access DB to a shell that simply links to Oracle and SQL Server 7 tables and performs a straight pipe of the raw data between DBs. However, now IS will be very interested (since it's SQL 7) and have Admininstrator rights, therefore causing the HR people to squash the deal. How can I lock SQL 7 up so tight that IS can't get to the data and yet be able to maintain the DB? If this is not feasible, are there any other options that might provide a solution?
Hi all, We have sql server 2000 on Windows server 2003.Is there anyway in sql server 2000 to protect some crucial data, even from the DBA. Thanks in advance...
Hello. I have a report with parameter called "parm1", that gets a value of "true" or "false" depanding on another parameter.
When the report is runnig the parm1 value is "false". How can I protect this parameter from a change by the user? I mean - the user can run the report and then add to the url "¶m1 = true".
Can I do anything against that? I tried marking it as "internal" and I thought that now it can get his value only from inside the report but it didn't worked.
Hello. I have a report with parameter called "parm1", that gets a value of "true" or "false" depanding on another parameter.
When the report is runnig the parm1 value is "false". How can I protect this parameter from a change by the user? I mean - the user can run the report and then add to the url "¶m1 = true".
Can I do anything against that? I tried marking it as "internal" and I thought that now it can get his value only from inside the report but it didn't worked.
I have designed a Microsoft SQL Server 2005 database application using Visual Basic 2005. I want to control access to the database programmatically, without the End-User opening the database in SQL Server.
I want to protect the database structure such as my tables, code, etc. This restriction should include all the Administrators of the Computers on which my application will be deployed. Any modification of my database or code should be implemented only by me.
What is the best way to do this using (a) Windows Authentication Login? (b) SQL Server Login? How do I configure the User-Login?
NEW: In addition to above question, how best do I achieve this protection if installing the DB with other databases in an already existing server, is it possible to remove the Builtin Admin from the server role?? As in my case, there is no need for anyone else to open the DB in Management Studio at all as my VB application does all that is required.
I want only SQL Server Authentication not Windows Authentication Because If some one copy database and attach at some other place using Windows Authentication then they can see each and everything.
I want something like Access (I know its password can be broken very easily)
I want to protect Table & SP Schema, Data is not much important.
We wrote an erp,and provide a platform to participator to extend my erp system,so I will give my participator database dictionary,but I only want to give partial database dictionary,I will hide some table and some field, I want they cann't open the database thouth sql server management studio or other tools,only can using our interface to access database,how can I do?
I have a DB on my SQL Server Express 2005. In this db I have one table and I DON'T want any user can modify data on this table but I want only show this data (only select statements allowed). If I install this db on one of my customers' machine, I can see that he can modify data into this table If he log in into the database with windows authentication and not with the "USERLOGIN" that I have created with sql server authentication. What can I to to remove dbo access in Windows authentication in my db and "transfer" the dbo in another user access (like MYUSER with Sql Server authentication)?
I have some tables in the employee database, this database created from sql sever 2000. I build a employee management application by C# and sqlserver 2000.
My goal is after design complete the empployee database by sqlserver 2000, any users can not modify my tables and unkonw table's structure. help me please
We have a table which needs to be updated 2 million times per day. It hosts all real time transaction. There are 200K records in this table. Would you please to share your experience with me about how to protect/save such table in SQL 2000 from any possible damage?
We plan to use point-in-time backup (every 5 minutes). It still takes at half an hour to recover the whole database. Any new technology from Microsoft or SQL 2000 you can recommend?
This post contains the code for this thread: http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=14475
It deals with the problem how to prevent log actions in long running batch jobs from being rolled back. It was heavily inspired by Andy Pope´s approach to error handling (http://www.sqlteam.com/item.asp?ItemID=2290) and in fact you will see much of his code here.
The code:
This procedure dynamically opens a second connection in parallel to the existing connection of the calling procedure using SQL-DMO. So the second connection runs without the scope of transaction of the calling procedure. So no action you take here is rolled back in case the calling proc fails. So be careful! Keeping data integrity is your job here and you could do many weird things to your database. The procedure dynamically adds a user function that if called just would return the object token of the new DMO connection. So any piece of code in the same batch could reuse the exisiting connection.
LogConstructor CREATE PROCEDURE LogConstructor AS
if exists (select * from sysobjects where id = object_id (N'dbo.MFF_GetLogObject') and OBJECTPROPERTY(id, N'IsScalarFunction') = 1) drop function dbo.MFF_GetLogObject
-- Create the SQLServer object EXEC @Error = sp_OACreate 'SQLDMO.SQLServer', @oSQLServer OUT IF @Error <> 0 GOTO OA_Error
-- Set the login process to use NT Authentication EXEC @Error = sp_OASetProperty @oSQLServer, 'LoginSecure', -1 IF @Error <> 0 GOTO OA_Error
-- Connect to server using NT Authentication EXEC @Error = sp_OAMethod @oSQLServer, 'Connect', NULL, @@SERVERNAME IF @Error <> 0 GOTO OA_Error
-- Verify the connection EXEC @Error = sp_OAMethod @oSQLServer, 'VerifyConnection', @Return OUTPUT IF @Error <> 0 GOTO OA_Error IF @Return = 0 GOTO OA_Error
-- Create Function with server object select @dynsql = N'CREATE Function MFF_GetLogObject () RETURNS INT AS BEGIN RETURN ' + cast(@oSQLServer as varchar) + N' END' EXEC sp_executesql @dynsql
return
OA_Error: -- Get the error text EXEC sp_OAGetErrorInfo @oSQLServer, @Source OUT, @ErrorMsg OUT SELECT @ErrorMsg = CONVERT(CHAR(16), @Error) + ': ' + @ErrorMsg + ' (Source: ' + @Source + ')' print @ErrorMsg return GO The next procedure just drops the DMO connection and also drops the user function as the token is invalid by now. This proc should be called within the same batch as the constructor to clean things up properly.
LogDestructor
CREATE PROCEDURE MFP_LogDestructor AS
declare @lo int select @lo = dbo.MFF_GetLogObject() exec sp_OADestroy @lo
if exists (select * from sysobjects where id = object_id(N'dbo.MFF_GetLogObject') and OBJECTPROPERTY(id, N'IsScalarFunction') = 1) drop function dbo.MFF_GetLogObject GO
Hello,How to protect structures(Tables,SP,Views and Functions) of a SQLServer Database?(Password protect a database file)I have a SQL database that will distribute with my application, I wantto protects it's structure from my appliction users. Only myapplication can access the database.Thanks
I have developed a small desktop application using c# and Ms Access 2002. Database is password protected and contains sensitive data. As many password retrieval tools are available, What should I do to protect Ms-Access (.MDB ) file? Is there any way through which I can hide database file
