Preventing SQL Injection in ASP.Net Preventing SQL Injection in ASP.Net sql sql

Preventing SQL Injection in ASP.Net

Try using a parameterized query here is a link

Also, do not use OpenQuery... use the this to run the select

SELECT * FROM db...table WHERE ref = @ref AND bookno = @bookno

More articles describing some of your options:

What is the T-SQL syntax to connect to another SQL Server?


Note: Your original question was asking about distributed queries and Linked servers. This new statement does not reference a distributed query. I can only assume you are directly connecting to the database now. Here is an example that should work.Here is another reference site for using SqlCommand.Parameters

SqlCommand cmd = new SqlCommand("Select * from Table where ref=@ref", con); cmd.Parameters.Add("@ref", SqlDbType.Int);cmd.Parameters["@ref"] = 34;


Ok Jamie taylor I will try to answer your question again.

You are using OpenQuery becuase you are probably using a linked DB

Basically the problem is the OpenQuery Method takes a string you cannot pass a variable as part of the string you sent to OpenQuery.

You can format your query like this instead. The notation follows servername.databasename.schemaname.tablename. If you are using a linked server via odbc then omit databasename and schemaname, as illustrated below

    Dim conn As SqlConnection = New SqlConnection("your SQL Connection String")    Dim cmd As SqlCommand = conn.CreateCommand()    cmd.CommandText = "Select * db...table where investor = @investor"    Dim parameter As SqlParameter = cmd.CreateParameter()    parameter.DbType = SqlDbType.Int    parameter.ParameterName = "@investor"    parameter.Direction = ParameterDirection.Input    parameter.Value = 34

Use parameters instead of concatenating your SQL query.

Assuming your database engine being SQL Server, here's a piece of code which I hope will help.

Using connection As SqlConnection = new SqlConnection("connectionString")    connection.Open()    Using command As SqlCommand = connection.CreateCommand()        string sqlStatement = "select * from table where ref = @ref and bookno = @bookno";        command.CommandText = sqlStatement        command.CommandType = CommandType.Text        Dim refParam As SqlDataParameter = command.CreateParameter()        refParam.Direction = ParameterDirection.Input        refParam.Name = "@ref"        refParam.Value = Ref        Dim booknoParam As SqlDataParameter = command.CreateParameter()        booknoParam.Direction = ParameterDirection.Input        booknoParam.Name = "@bookno"        booknoParam.Value = Session("number")        Try            Dim reader As SqlDataReader = command.ExecuteQuery()            ' Do your reading job here...'        Finally            command.Dispose()            connection.Dispose()        End Try    End UsingEnd Using

To sum it all up, avoid SQL statement concatenation at all cost, and use parameterized quesries!

Here is an interesting link that brings you through SQL injection problem resolution on MSDN:

How To: Protect From SQL Injection in ASP.NET

use sqlparameters like:

SqlCommand cmd = new SqlCommand("Select * from Table where id=@id", con);cmd.Parameters.AddWithValue("@id", 34);