mssql SQL Injection

[bluewalrus]

Hey,

Anyone have any ideas on how I should go about preventing SQL injections on a microsoft sql database? I've used settype to integers for values that are only IDs but I'm know using string inputs and am not sure how to pass them. Thanks.

[djr33]

If none of your queries involve any direct user input except for values within quotes, then all you need to escape, generally speaking, is quotes.

So just do str_replace("'","\'",$userinput);

Of course this won't handle anything except that which you place directly in single quotes, such as:
$query = "SELECT * FROM `table` WHERE `col`='$value';";

However, I expect that there may be more info available on google for this since mssql is fairly popular. There may be a nice function like mysql_real_escape_string, but I don't know.

[katierosy]

This area of awareness is similar , as it has been already answered in this forum.
If we will escape some strings, we will be able to avoid this. In mysql we use mysql_real_escape_string to do this, similarly there may be some functions, if not we may adopt our own methods as shown in this forum , in order to escape these string.

Hope this much will help.

[bluewalrus]

Okay, I've returned to this again...

If I were to input

O'Brien

into a database of users

would using

$user_first = $_POST['fname'];
$user_last = $_POST['lname'];
$user_first = str_replace("'", "''", $user_first);
$user_last = str_replace("'", "''", $user_last);
// those has a ' for the first value and two for the second (in each string replacement).

I want to use these values in a query such as

$query = "INSERT into Users_Table (F_Name, L_Name) values ($user_first, $user_last)"; 

or possibly

$query = "update Users_Table set F_Name = '$user_first', L_Name = '$user_last' where users_id_is = $####"; 

Thanks.

[djr33]

Why are you replacing it with two single quotes? You should use \' instead. The slash escapes it. The doubling just duplicates the problem.

[bluewalrus]

Okay, thanks I think I was thinking the \ was to escape the php not the sql. Thanks for clearing that up. Would this be about the equivalent of mysql_real_escape or are there others I should escape? In the examples I see online all injections are triggered from a single quote being parsed in.

[djr33]

You could have a similar situation with a double quote (if you were using those instead). There are also some special key-symbols (keywords?) including _ and % as wildcards. I'm not sure about the rest. Simply put, it's a lot easier and safer to use mysql_real_escape_string(). Of course it's annoying to type that, but it's reliable.


In short, more or less, it's the same thing. But there might be some situations where you can't predict what sort of other characters might have an effect. Whatever you do, make sure to escape the single quotes... and the rest if you can...

[bluewalrus]

Oh, okay thanks. I'm not using mysql though do you know if the function is still avaliable?

I read the mysql_real_escape documentation and it escapes for the following things

\x00, \n, \r, \, ', " and \x1a

Do you know what the \x00, and the \x1a do? I figure I can replace the \n and \r with <br />.

It doesn't escape for those other wild cards you mentioned. It says those are used with LIKE, GRANT, or REVOKE. Those are contained in single quotes to be triggered which would already be escaped correct?

[djr33]

They're some sort of control characters. I'm not sure on the details.

From my minimal understanding, mysql_real_escape_sting() is actually a variable function in that it may work differently in different configurations. I have no idea if this is really true or how much it varies, but the idea is that it is related to your current mysql connection. That said, you probably can't use it or wouldn't want to rely on it.

If you look around you may find a substitute for mssql because this must be a popular function.

[bluewalrus]

Okay, thanks. I think I'll write a number of string replaces. I'm not really finding any results for a mssql equvilant of mysql_real_escape_string, googling mysql_real_escape_string mssql first result brings me to this thread.

If an input were \' it would then become \\'. Would this still escape the single quote or would the first slash be escaped and make the second quote active?