Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
parrot
Posting Yak Master
132 Posts |
 Posted - 2013-01-04 : 15:18:34
|
| I posted a topic yesterday where I asked if the following statement is subject to sql injection.string strSQL = "INSERT INTO Mytable (Code, inputdata)";strSQL += " VALUES ('1234', ?)";OleDbCommand myCommand = new OleDbCommand(strSQL, OleDbConn1);myCommand.Parameters.Add("@mydata", OleDbType.VarChar, 6);myCommand.Parameters["@mydata"].Value = inputdat.Text;I was told that this is a positional parameterized code which is subject to sql injection and I should use sqlcommands instead with scalar @inputdata. However, research on the internet indicates that the use of the positional ? is valid prevention for sql injection using Oledbcommands in the same way that the use of scalar @ is used with sqlcommands. So who is right? Is the use of positional ? dangerous? I want to be sure of this before I start changing massive amounts of code. One of my sources for this information is at https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet.Dave |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2013-01-04 : 16:03:16
|
OWasp is a trustworthy source, if it's easier to use ? then go ahead. However, please be aware of string concatenation. The safe OWasp examples don't use it, and one unsafe example does. The safest way is to have a single command:string strSQL = "INSERT INTO Mytable (Code, inputdata) VALUES ('1234', ?)";This improves the chance that you have a correct SQL statement and limits the possibility for inadvertent errors or injection avenues. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
parrot
Posting Yak Master
132 Posts |
Posted - 2013-01-04 : 16:37:21
|
| I guess I don't know why my example uses string concatenation.string strSQL = "INSERT INTO Mytable (Code, inputdata)";strSQL += " VALUES ('1234', ?)";OleDbCommand myCommand = new OleDbCommand(strSQL, OleDbConn1);myCommand.Parameters.Add("@mydata", OleDbType.VarChar, 6);myCommand.Parameters["@mydata"].Value = inputdat.Text;The ? is used the same as the scalar@ in sqlcommand. It points to a positional parameter doesn't it? How else would I code the above? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2013-01-04 : 17:03:58
|
| [code]string strSQL = "INSERT INTO Mytable (Code, inputdata) VALUES ('1234', ?)";OleDbCommand myCommand = new OleDbCommand(strSQL, OleDbConn1);myCommand.Parameters.Add("@mydata", OleDbType.VarChar, 6);myCommand.Parameters["@mydata"].Value = inputdat.Text;[/code]Notice it's not doing any strSQL += type stuff. That's concatenation, and that's usually where injection can sneak in. You want valid SQL statements encapsulated in a single string. Even if concatenation yields the same result, it's still safer to reduce or eliminate its use. It's an ounce of prevention during coding. If you never concatenate, you almost guarantee against injection. |
|
|
|
parrot
Posting Yak Master
132 Posts |
Posted - 2013-01-04 : 17:08:08
|
| Thanks for your feedback. In a word, do not use strSQL += anywhere just use strSQL = the whole damn string. I thought you were referring to concatenating fields rather than the instruction string. So I still have some work do to but not as much as having to convert everything from oledbcommands to sqlcommands. Thanks again. |
|
|
|
|
|
|
|
|
sql injection revisited