How do I parameterize complex OleDB queries?
I am trying to refactor some code that uses string concatenation to generate SQL commands (making it vulnerable to SQL injection). Basically all I am trying to do is replace all statements string sqlToExecute = String.Format(..)
with SQL command and OleDB list parameters.
I understand how this can be done for simple cases like String.Format("Select * from myTable where id = {0}", id)
. However, I couldn't find a set of good examples for more complex SQL queries.
Here are some of the queries I'm not sure exactly how I can parameterize:
1. Parameters are used for both the column name and the alias; The parameter consists of two variables:
selQueryBldr.AppendFormat("SELECT * FROM {0} {1}
INNER JOIN ColChange CC ON CC.TableRecordID = {1}.{2} and CC.EntityID='{3}'",
entity.StageTableName, stageTableAlias, entity.PrimaryKey, entity.EntityID);
2. The same parameter is used in several SQL IN clauses
SQL query:
SELECT A.TablePrefix ...
FROM Entity E
INNER JOIN App A
ON A.AppID = E.AppID
WHERE E.AppID in (#APPIDS#)
UNION
SELECT A.TablePrefix ...
FROM EntityB EB
INNER JOIN App A
ON A.AppID = EB.AppID
WHERE EB.AppID in (#APPIDS#)
The parameter is currently being added to the code using the String.Replace () method:
sqlQuery = sqlQuery.Replace("#APPIDS#",idList);
3. Using variables as parameter name and parameter value:
StringBuilder dataQuery = new StringBuilder("Select * from {0} WHERE {1}='{2}'",
tableName, primaryKey[0], changeRow["TableRecordID"]);
4. The variable used part of the unicode parameter:
sSQL = string.Format("SELECT name FROM sysobjects WHERE id = object_id(N'[dbo].[{0}]')",
sSPName);
Also, all of these examples use OleDb classes (OleDbConnection / OleDbCommand etc.), so as far as I understand named parameters cannot be used here.
source to share
The different ends allow (or not) either named parameters or "?" place-holder for parameters, so what you would do is construct your request something like
OleDbCommand oCmd = new OleDbCommand( YourConnection, "select * from someTable where yourColumn = ? and otherColumn = ?" );
oCmd.Parameters.AddWithValue( "parm1", YourVariable.FormattedHoweverNeeded );
oCmd.Parameters.AddWithValue( "parm2", anotherVariable.FormattedHoweverNeeded );
If columns expect rows, make sure row. If you are expecting numeric (int, double, float, etc.) leave them as this type or others (date / time, etc.)
Just note ... if you don't execute named parameters (like I did with "?"), The parameters must be added in the same sequence as "?" are placed in the SQL command.
source to share