Protect from SQL Injection

[bdichiara]

I looked into this and it seems like this is the way to protect myself

Dim sqlStatement As New Odbc.OdbcCommand("SELECT * FROM content LEFT JOIN categories ON content.category = categories.categoryid WHERE categories.cat_name = @category AND content.name = @page AND content.deleted <> '1' LIMIT 0,1")
		sqlStatement.Parameters.Add("category", Odbc.OdbcType.VarChar).Value = categoryStr
		sqlStatement.Parameters.Add("page", Odbc.OdbcType.VarChar).Value = pageStr

however, now I'm having trouble with my connection. Before, the variable "sqlStatement" was just a string and I was using:

MyDataAdapter = New Odbc.OdbcDataAdapter(sqlStatement, MyConnection)

However, now it's a command, so how do I get this to execute properly? I've tried adding sqlStatement.toString, but that doesn't help.

_______________
_brian.

 

[josie2007]

Take a look at this code...
Dim connectionString As String = ConnectionStrings("ConnectionString").ConnectionString
Dim oOracleConn As OracleConnection = New OracleConnection(connectionString)
oOracleConn.Open()

Dim strStringBuilder As StringBuilder
strStringBuilder = New StringBuilder
With strStringBuilder
.Append(" SELECT DISTINCT G.CSECNUM ""Section Number"", E.IPLINENO ""Line Number""")
.Append(" , (SUBSTR (e.eiitem, 1, 4) || '.'|| SUBSTR (e.eiitem, 5, 9) )""Item Number"", (i.idescrl ||' '|| e.isupdes) ""Item Description"" , ")
.Append(" I.IUNITS ""Item Units"" ,sum(E.IQTY) ""Quantity"", P.CPROJNUM ""S.P. Number"",P.CONTID , ")
.Append(" decode(trim(P.PRROUTE),null, 'N/A', P.PRROUTE) ""Route Number"", ")
.Append(" INITCAP(p.clocat1||p.clocat2||'---'||P.CDESCR) ""loc"", P.CDESCR ""Job Description"",")
.Append(" FUNC_GET_UNIT_NAME(IUNITS) ""Unit Name"", ")
.Append(" (select initcap(FUNC_GET_COUNTY_NAME(CCNTY1)) ")
.Append(" FROM proposal where contid =:ContractNumber) ""County Name"" ")
.Append(" FROM ITEMLIST I,ESTITEM E,ESTCATG G,PROPPROJ X,PROPOSAL P ")
.Append(" WHERE(P.CONTID = X.CONTID And X.PCN = G.PCN And E.PCN = G.PCN And E.CN = G.CN) ")
.Append(" AND I.ITEM = E.EIITEM AND I.ISPECYR = P.CSPECYR AND E.IPLINENO <>' ' ")
.Append(" AND E.EIITEM <> '2550601/01000' AND E.EIITEM <> '2565601/00031' ")
.Append(" AND E.EIITEM <> '2565601/00032' AND E.EIITEM <> '2565601/00033' ")
.Append(" AND E.EIITEM <> '2402601/01000' ")
.Append(" AND SUBSTR(P.CONTID,4,1) < 5 AND P.contid = :ContractNumber")
.Append(" group by G.CSECNUM, I.IUNITS,p.clocat1,p.clocat2,e.isupdes, P.CONTID,P.CDESCR, P.CPROJNUM, I.IUNITS,P.PRROUTE, E.IPLINENO, E.EIITEM, I.IDESCRL ")
.Append(" order by 2")
End With

'CREATE A NEW COMMAND AND PASS THE SQL STATEMENT / CONNECTION OBJECT
Dim cmdItemDetail As OracleCommand = New OracleCommand()
'cmdItemDetail.Parameters.AddWithValue(":ContractNumber", ContractId)
cmdItemDetail.Parameters.Add("ContractNumber", OracleType.Char).Value = ContractId
cmdItemDetail.Connection = oOracleConn
cmdItemDetail.CommandType = CommandType.Text
cmdItemDetail.CommandText = strStringBuilder.ToString
Dim adItemDetail As New OracleDataAdapter(cmdItemDetail)
Dim dsItemDetail As New DataSet

'fill the dataset with the result of our query from the specified command
adItemDetail.Fill(dsItemDetail, "ItemDetails")

'Bind the DataSet to the GridView
gvItem.DataSource = dsItemDetail
gvItem.DataBind()

 

[monksnake]

Try this, put:

Dim sqlStatement As New [!]SqlCommand[/!](query, connection)

<.

 

[GoTerps88]

Below is a link regarding preventing SQL Injection within ASP.NET.

http://msdn2.microsoft.com/en-us/library/ms998271.aspx

And the reason your command isn't working is because you aren't specifying a connection.

I'm not sure why you are using the ODBC Command but for the SQLCommand you would create a command object like this:

SqlConnection objConn = new SqlConnection(strConn);
SqlCommand objCmd = new SqlCommand(strSQL,objConn);
objConn.Open();

where strSQL is a string variable pointing to your sql statement and objConn is a variable pointing to your connection object.