title: Preventing SQL Injection in ASP.Net
body: <p>I have this code</p>
  
  <pre><code>UPDATE OPENQUERY (db,'SELECT * FROM table WHERE ref = ''"+ Ref +"'' AND bookno = ''"+ Session("number") +"'' ') 
  </code></pre>
  
  <p>How would I prevent SQL Injections on this?</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>Here's what i'm trying</p>
  
  <pre><code>SqlCommand cmd = new SqlCommand("Select * from Table where ref=@ref", con); 
  cmd.Parameters.AddWithValue("@ref", 34);
  </code></pre>
  
  <p>For some reason everything I try and add it doesn't seem to work I keep getting <code>SQL Command</code> mentioned below.</p>
  
  <p>The error is this</p>
  
  <pre><code>'SqlCommand' is a type and cannot be used as an expression
  </code></pre>
  
  <p>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.</p>
  
  <p><strong>UPDATE NO 2</strong></p>
  
  <p>I added in the code as VasilP said like this</p>
  
  <pre><code>Dim dbQuery As [String] = "SELECT * FROM table WHERE ref = '" &amp; Tools.SQLSafeString(Ref) &amp; "' AND bookno = '" &amp; Tools.SQLSafeString(Session("number")) &amp; "'"
  </code></pre>
  
  <p>But I get an error <code>Tools is not declared</code> do I need to specify a certain namespace for it to work?</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>Has anyone got any ideas on the best of getting my query safe from SQL injection without the errors that i'm experiencing?</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>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?</p>
  
  <pre><code>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()
  </code></pre>
  
  <p>It works like this</p>
  
  <pre><code>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()
  </code></pre>
  
  <p>The error i'm getting is this </p>
  
  <pre><code>An error occurred while preparing a query for execution against OLE DB provider 'MSDASQL'. 
  </code></pre>
  
  <p>And it's because it isn't replacing the <code>@investor</code> with the <code>69836</code></p>
  
  <p>Any ideas?</p>
  
  <p><strong>SOLUTION</strong></p>
  
  <p>Here is how I solved my problem</p>
  
  <pre><code>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()
  </code></pre>
  
  <p>Now I can write queries without the worry of SQL injection</p>