I have this code
UPDATE OPENQUERY (db,'SELECT * FROM table WHERE ref = ''"+ Ref +"'' AND bookno = ''"+ Session("number") +"'' ')
How would I prevent SQL Injections on this?
UPDATE
Here's what i'm trying
SqlCommand cmd = new SqlCommand("Select * from Table where ref=@ref", con);
cmd.Parameters.AddWithValue("@ref", 34);
For some reason everything I try and add it doesn't seem to work I keep getting SQL Command
mentioned below.
The error is this
'SqlCommand' is a type and cannot be used as an expression
I'm taking over someone else's work so this is all new to me and I would like do things the right way so if anyone can provide any more help on how to make my query above safe from SQL injections then please do.
UPDATE NO 2
I added in the code as VasilP said like this
Dim dbQuery As [String] = "SELECT * FROM table WHERE ref = '" & Tools.SQLSafeString(Ref) & "' AND bookno = '" & Tools.SQLSafeString(Session("number")) & "'"
But I get an error Tools is not declared
do I need to specify a certain namespace for it to work?
UPDATE
Has anyone got any ideas on the best of getting my query safe from SQL injection without the errors that i'm experiencing?
UPDATE
I now have it so it work without the parameters bit here's my updated source code any idea why it won't add the parameter value?
Dim conn As SqlConnection = New SqlConnection("server='server1'; user id='w'; password='w'; database='w'; pooling='false'")
conn.Open()
Dim query As New SqlCommand("Select * from openquery (db, 'Select * from table where investor = @investor ') ", conn)
query.Parameters.AddWithValue("@investor", 69836)
dgBookings.DataSource = query.ExecuteReader
dgBookings.DataBind()
It works like this
Dim conn As SqlConnection = New SqlConnection("server='server1'; user id='w'; password='w'; database='w'; pooling='false'")
conn.Open()
Dim query As New SqlCommand("Select * from openquery (db, 'Select * from table where investor = 69836') ", conn)
dgBookings.DataSource = query.ExecuteReader
dgBookings.DataBind()
The error i'm getting is this
An error occurred while preparing a query for execution against OLE DB provider 'MSDASQL'.
And it's because it isn't replacing the @investor
with the 69836
Any ideas?
SOLUTION
Here is how I solved my problem
Dim conn As SqlConnection = New SqlConnection("server='h'; user id='w'; password='w'; database='w'; pooling='false'")
conn.Open()
Dim query As New SqlCommand("DECLARE @investor varchar(10), @sql varchar(1000) Select @investor = 69836 select @sql = 'SELECT * FROM OPENQUERY(db,''SELECT * FROM table WHERE investor = ''''' + @investor + ''''''')' EXEC(@sql)", conn)
dgBookings.DataSource = query.ExecuteReader
dgBookings.DataBind()
Now I can write queries without the worry of SQL injection
The method which many ASPNET applications use to access MSSQL databases also makes them generally resistant to SQL injection attacks. But it is still POSSIBLE to create a vulnerable application if you are careless enough.
How to Prevent an SQL Injection. The only sure way to prevent SQL Injection attacks is input validation and parametrized queries including prepared statements. The application code should never use the input directly. The developer must sanitize all input, not only web form inputs such as login forms.
The way to protect against SQL injection is to use parameterized queries instead of string concatenation. So in this case if we changed our SQL statement to something like this, var users = "select * from users where userId = @userId"; then the attacker could not modify the final result.
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 Using
End 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
Try using a parameterized query here is a link http://www.aspnet101.com/2007/03/parameterized-queries-in-asp-net/
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:
http://support.microsoft.com/kb/314520
What is the T-SQL syntax to connect to another SQL Server?
Edited
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;
Edited:
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With