From http://www.tizag.com/mysqlTutorial/mysql-php-sql-injection.php I got:

SQL injection refers to the act of someone inserting a MySQL statement to be run on your database without your knowledge. Injection usually occurs when you ask a user for input, like their name, and instead of a name they give you a MySQL statement that you will unknowingly run on your database.

I read the whole article but I still have some major issues understand what it is and how can it be done.

In the first example, what will they actually see?

As far as i understood, if I actually echo $name, the will see all the names because it will always "be true" am I correct?

The other thing I dont understand is whether THE MySQL injection problem is solved with mysql_real_escape_string(), there has to be more to it.

What I really dont get is that mysql_real_escape_string() is made to solve that issue, why isn´t this done automatically, I mean is there a reason you have to add every time mysql_real_escape_string(), is there cases when you should use it and thats why they dont make this automatic?

I hope the question is clear enough, maybe my luck of understanding of the topic makes the question confusing so please ask for any clarification if necessary!

Thanks in advance!!

MySQL won't escape automatically, because you build the query string yourself. For example:

$query = 'SELECT * FROM users WHERE name="' . $name . '"';

You just pass the raw string stored in $query, which is open to SQL injection. For example, if $name is [something" OR "1=1] your query string ends up being:

$query = 'SELECT * FROM users WHERE name="something" OR "1=1"

That would return every user from the user table. Which is why you need to escape values. However, if you use PDO, it is done for you if you use the binding functionality. It's a 2 step process, preparing the querying, then "binding" the data/variables to the placeholders. In PDO, your query string would look something like this:

$query = 'SELECT * FROM users WHERE name=":name"';
$bindings = array('name'=>'something');
prepare($query);
execute($bindings);

Then, things are automatically escaped for you.

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?

Thanks

Jamie

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.

Thanks

Jamie

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?

Thanks

Jamie

UPDATE

Has anyone got any ideas on the best of getting my query safe from SQL injection without the errors that i'm experiencing?

Thanks

Jamie

UPDATE

I now have it so it work without the parameters bit here's my updated souce 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

Thanks everyone

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

http://stackoverflow.com/questions/125457/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