I have a situation need your help. At the moment, i've build an asp.net app using ado.net. I'm using CommandText to build dynamic query so it have SQL Injection vulnerability. My CommandText like this
String.Format("SELECT COUNT(*) FROM {0} {1}", tableName, whereClause)
TableName and whereClause is passed in by developer. As you see I cannot use SQLParameters here because I need to pass entire tableName and whereClause not only parameter values.
My solution to prevent SQL Injection is using BlackList check TableName and whereClause to find out malicious string but I don't know this is the best way in this situation, isn't it. And if it is anyone can help me where to find BlackList references or library.
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:
sys.tables
system table (in SQL Server, other DBMS might have similar tables). In this query, you can use parameters so you are safe.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).WHERE
clause as you'd basically have to parse the SQL WHERE clause and assert that no dangerous code is contained.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.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.