Removing the concatenated code variable for a SQL select statement .
-
I know I should use a stored procedure to do this but ths is not possible. In the code below return values from the "select" statements are being assigned to code variable values being concatenated with the actual Select statement. I am not talking about the code in the Where clause but the code in the "Select" portion. See the string as below:
query.Append(" SELECT e581_key, 'e581', 'rnlt', rnlt, '" + _rnlt + "' , 'SaveEditRNLT', '" + _logUserName + "', getdate() ");
query.Append(" FROM e581 ");The select statement is then assigned to the DbCommand.CommandText of the command.ExecuteNonQuery.
How do I move the actual code variables being concatenated to some type of command parameter?
-
I know I should use a stored procedure to do this but ths is not possible. In the code below return values from the "select" statements are being assigned to code variable values being concatenated with the actual Select statement. I am not talking about the code in the Where clause but the code in the "Select" portion. See the string as below:
query.Append(" SELECT e581_key, 'e581', 'rnlt', rnlt, '" + _rnlt + "' , 'SaveEditRNLT', '" + _logUserName + "', getdate() ");
query.Append(" FROM e581 ");The select statement is then assigned to the DbCommand.CommandText of the command.ExecuteNonQuery.
How do I move the actual code variables being concatenated to some type of command parameter?
You can't use SQL-parameters for column names. But if the value for these variables can not come from user input, then there's no risk of SQL-injection (I assume that's why you're asking). If you can guarantee this, then you'll be fine here. But there are some other strange things: - You're selecting the column "rnlt" at least twice. - You're trying to select a column that has the name of a (the current?) date? - "e581" seems to be a column name and a table name?
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
-
I know I should use a stored procedure to do this but ths is not possible. In the code below return values from the "select" statements are being assigned to code variable values being concatenated with the actual Select statement. I am not talking about the code in the Where clause but the code in the "Select" portion. See the string as below:
query.Append(" SELECT e581_key, 'e581', 'rnlt', rnlt, '" + _rnlt + "' , 'SaveEditRNLT', '" + _logUserName + "', getdate() ");
query.Append(" FROM e581 ");The select statement is then assigned to the DbCommand.CommandText of the command.ExecuteNonQuery.
How do I move the actual code variables being concatenated to some type of command parameter?
You need to pass the variables as parameters, in exactly the same way as you would for the
WHEREclause:using (var command = new SqlCommand("", connection))
{
var query = new StringBuilder();
...
query.Append(" SELECT e581_key, 'e581', 'rnlt', rnlt, @rnlt, 'SaveEditRNLT', @logUserName, getdate() ");
query.Append(" FROM e581 ");
command.Parameters.AddWithValue("@rnlt", _rnlt);
command.Parameters.AddWithValue("@logUserName", _logUserName);
...
command.CommandText = query.ToString();
...
}
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
You need to pass the variables as parameters, in exactly the same way as you would for the
WHEREclause:using (var command = new SqlCommand("", connection))
{
var query = new StringBuilder();
...
query.Append(" SELECT e581_key, 'e581', 'rnlt', rnlt, @rnlt, 'SaveEditRNLT', @logUserName, getdate() ");
query.Append(" FROM e581 ");
command.Parameters.AddWithValue("@rnlt", _rnlt);
command.Parameters.AddWithValue("@logUserName", _logUserName);
...
command.CommandText = query.ToString();
...
}
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
I don't understand what you mean. for instance take the code below:
query.Append("SELECT e581_key, 'e581', 'pickup_date', pickup_date , '" + _dateMaterialRequired + "' , 'SaveEditPickup', '" + _logUserName + "', getdate() ");
Now _dateMaterialRequired and _timeMaterialRequired are variables, properties, that belong to the to the class that called the hard coded SQL statement. When the SQL is called how are the select statement return values assigned to the variables, properties, that are in the calling code?
-
You need to pass the variables as parameters, in exactly the same way as you would for the
WHEREclause:using (var command = new SqlCommand("", connection))
{
var query = new StringBuilder();
...
query.Append(" SELECT e581_key, 'e581', 'rnlt', rnlt, @rnlt, 'SaveEditRNLT', @logUserName, getdate() ");
query.Append(" FROM e581 ");
command.Parameters.AddWithValue("@rnlt", _rnlt);
command.Parameters.AddWithValue("@logUserName", _logUserName);
...
command.CommandText = query.ToString();
...
}
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
I don't understand what you mean. for instance take the code below:
query.Append(" SELECT e581_key, 'e581', 'alloc_period', alloc_period , '" + _allocationPeriod.ToString() + "' , 'SaveEditCombatLoad', '" + _logUserName + "' , getdate() ");
query.Append(" FROM e581 ");Now _allocationPeriod.ToString() and SaveEditCombatLoad are variables, properties, that belong to the to the class that called the hard coded SQL statement. When you say columns returns by the SQL no columns exist It's just values in the class. Am I missing something??
-
I don't understand what you mean. for instance take the code below:
query.Append("SELECT e581_key, 'e581', 'pickup_date', pickup_date , '" + _dateMaterialRequired + "' , 'SaveEditPickup', '" + _logUserName + "', getdate() ");
Now _dateMaterialRequired and _timeMaterialRequired are variables, properties, that belong to the to the class that called the hard coded SQL statement. When the SQL is called how are the select statement return values assigned to the variables, properties, that are in the calling code?
What? :confused: The code you've shown passes the value of the variables from C# to SQL - using string concatenation, so it's vulnerable to SQL Injection. Those values are returned as part of the result-set of the query. Nothing in the code you've shown will update the value of the C# variables.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
What? :confused: The code you've shown passes the value of the variables from C# to SQL - using string concatenation, so it's vulnerable to SQL Injection. Those values are returned as part of the result-set of the query. Nothing in the code you've shown will update the value of the C# variables.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
In the select statement I did not include the "Where" clause by mistake. But these values are being filled in by the Where clause. Sorry.
-
In the select statement I did not include the "Where" clause by mistake. But these values are being filled in by the Where clause. Sorry.
No, that still doesn't make any sense.
holdorf wrote:
how are the select statement return values assigned to the variables, properties, that are in the calling code?
holdorf wrote:
But these values are being filled in by the Where clause.
Executing a SQL query will never automatically update the value of a C# variable.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
No, that still doesn't make any sense.
holdorf wrote:
how are the select statement return values assigned to the variables, properties, that are in the calling code?
holdorf wrote:
But these values are being filled in by the Where clause.
Executing a SQL query will never automatically update the value of a C# variable.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
I guess I am not making myself clear. This is working SQL code that update the C# variables from the query after it is called. What my job is to remove all of the SQL inject errors that exist from this working code.
-
I guess I am not making myself clear. This is working SQL code that update the C# variables from the query after it is called. What my job is to remove all of the SQL inject errors that exist from this working code.
No, what you have posted is working-but-vulnerable code which executes a query that includes the values of some C# variables in the results. At no point does the code you've posted update the value of any C# variables. Either you've forgotten to post that part of the code, or the code isn't doing what you think it's doing.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
