SqlParameter does not allows Table name - other options without sql injection attack?
Go for a white list. There can only be a fixed set of possible correct values for the table name anyway - at least, so I'd hope.
If you don't have a white list of table names, you could start with a whitelist of characters - if you restrict it to A-Z, a-z and 0-9 (no punctuation at all) then that should remove a lot of the concern. (Of course that means you don't support tables with odd names... we don't really know your requirements here.)
But no, you can't use parameters for either table or column names - only values. That's typically the case in databases; I don't remember seeing one which did support parameters for that. (I dare say there are some, of course...)
SQL Parameter as table doesn't work
You can't pass a table name as a parameter. Also, use using
to easily close/dispose of disposable resources.
Try this...
private DataTable GetTable(string tableName)
{
string queryString = "SELECT * FROM [" + tableName + "]";
DataTable dataTable = new DataTable(tableName);
using (SqlCommand sqlCommand = new SqlCommand(queryString, _sqlConnection))
using (SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlCommand))
{
_sqlConnection.Open();
dataAdapter.Fill(dataTable);
_sqlConnection.Close();
}
dataGridViewTable.DataSource = dataTable;
dataGridViewTable.AutoResizeColumns();
return dataTable;
}
EDIT: Added square brackets around table name in query to handle names with spaces.
How to use Select query for table names with parameters?
You can't pass an object name as a parameter directly to the statement like that. You can still use a parameter but the command will have to be something like this:
(@"declare @sql nvarchar(max) = N'create table dbo.'
+ QUOTENAME(@1) + N'([BACK_LANG] varchar(50));';
EXEC sys.sp_executesql @sql;")
Or just build the string in C#, using SqlCommandBuilder
's QuoteIdentifier(this.getName())
which provides similar protection as QUOTENAME()
.
For more on SQL injection: Dynamic SQL
Prevent SQL Injection when the table name and where clause are variables
Without knowing further details, there are several options you have in order to avoid SQL injections attacks or at least to minimize the damage that can be done:
- Whitelisting is more secure than blacklisting: Think about whether you really need access to all the tables except the blacklisted ones. If anyone adds tables at a later point in time, he or she might forget to add them to the backlist.
- Maybe you can restrict the access to a specific subset of tables. Ideally, these tables follow a common naming scheme so the table name can be validated against that scheme. If there is no naming scheme, you could also add a list of the tables that can be accessed in the program or the application configuration so you can check whether the table name is contained in this list. If you save the list in a configuration file, you are able to expand the list without compiling the application again.
- If you cannot whitelist the table names, you could at least check whether the supplied table name is present in the database by querying the
sys.tables
system table (in SQL Server, other DBMS might have similar tables). In this query, you can use parameters so you are safe. - For SQL Server, you should put the table name in square brackets (
SELECT COUNT(*) FROM [" + tableName + "]"
). Square brackets are used to delimit identifiers (also see this link). In order for this to work, you have to check that thetableName
variable does not contain a closing square bracket. If thetableName
variable might contain a schema identifier (e.g.dbo.MyTable
you'd have to split the parts first and then add the square brackets ([dbo].[MyTable]
) as these are separate identifiers (one for the schema, one for the table name). - Validate the contents of the variables very carefully by using regular expressions or similar checks. This is easy for the table name, but very hard for the
WHERE
clause as you'd basically have to parse the SQL WHERE clause and assert that no dangerous code is contained. - The hardest part is to check the
WHERE
clause. Also in this respect it would be best, if you could limit the options for the user and whitelist the possibleWHERE
clauses. This means that the user can choose from a range ofWHERE
clauses that the program knows or builds based upon the user input. These knownWHERE
clauses could contain parameters and therefore are safe against SQL injection attacks. If you cannot whitelist theWHERE
clauses, you'd have to parse theWHERE
clause in order to be able to decide whether a certain request is dangerous or not. This would require a large effort (if you don't find a library that can do this for you), so I'd try to whitelist as many parts of the dynamic query as possible. - In order to reduce the damage of a successful attack, you should run the query under a specific account that has very limited rights. You'd have to add another connection string to the config-file that uses this account and create the connection with the limited connection string. In SQL Server, you could move the tables that this account is able to access to a specific schema and limit the access to this schema for this account.
- Protect your service very well against unauthorized access so that only trusted developers can access it. You can do this by using some components in the infrastructure (firewalls, transport-level security etc.) and also by adding a strong user authentication mechanism.
- Log each request to the service so that the user and machine can be identified. Notify the users about this logging mechanism so that they know that they will be identified should anything go wrong.
Some final thoughts: even if it seems very easy to provide developers with such an open method for querying data, think about whether it is really necessary. One possible option would be to not have this open access, but instead configure the queries other developers need in a configuration file. Each query gets an identifier and the query text is stored in the file and therefore known beforehand. Still, you are able to add new queries or change existing ones after you have deployed the service. You can allow parameters in the query that the callers specify (maybe a numbered parameter scheme like p1, p2, ...).
As you can see from the list above, it is very hard (and in some areas close to impossible) to lock the service down and avoid all kinds of SQL injection attacks once you allow this open access. With an approach as described in the last paragraph you loose some flexibility, but you wouldn't have to worry about SQL injection attacks anymore.
Avoid SQL Injections on query with tablename
I question why you are doing this, but you can look at sys.tables
for a conclusive whitelist.
DECLARE @TableName VARCHAR(100) = 'Table to Look for';
DECLARE @Exists BIT = ( SELECT CAST( COUNT(1) AS BIT ) FROM sys.tables WHERE name = @TableName AND type = 'U' );
You could parameterize the initial input, but the whitelist approach is still important. Otherwise, a malicious user could pass any valid table name in the entire database and the query would run against it (assuming they had SELECT permissions).
Use sql table like sql parameter in vb.net
I have it
Dim tableName As String = "Test" + strIdFixed.ToString.Trim
Dim builder = New SqlCommandBuilder()
Dim escapedTableName As String = builder.QuoteIdentifier(tableName)
myCommand = myConnection.CreateCommand()
myCommand.CommandType = CommandType.Text
myCommand.CommandText =
"SELECT Id, Bez, Param5, Info from " + escapedTableName
Thank https://stackoverflow.com/a/17948039/6787667
Related Topics
How to Connect to ASP.NET Development Server Issue
Minimal and Correct Way to Map One-To-Many with Nhibernate
Why Must We Define Both == and != in C#
ASP.NET Webapi: How to Perform a Multipart Post with File Upload Using Webapi Httpclient
How to Generate a Constructor from Class Fields Using Visual Studio (And/Or Resharper)
How to Data Bind a List of Strings to a Listbox in Wpf/Wp7
Entity Framework/Linq to SQL: Skip & Take
Get Error Message If Modelstate.Isvalid Fails
How to Increase Executiontimeout for a Long-Running Query
How to Get the Taskbar's Position and Size
Reading a Key from the Web.Config Using Configurationmanager
How to Compare Unicode Characters That "Look Alike"
Self Referencing Loop Detected - Getting Back Data from Webapi to the Browser
Does ASP.NET MVC Have Application Variables