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?

[traq]

" single quote, double quote, slash, back slash, semi colon, extended character like NULL, carry return, new line, etc " is what I've found. Doesn't seem to be a stock function comparable to mysql_real_escape_string(). Microsoft (those loveable hunks) suggest using stored procedures and validating all inputs using custom-built regexes. I'm sure you've got enough free time for that.

[bluewalrus]

haha yea, plenty of time for that. I've just gotten back to this thread now. So going further into this...


I've started used parametrized queries as an alternative to escaping all the possible inputs.

How secure is this method, if at all?

Side not for mssql users stumbling upon this, mssql doesn't take the \' as escaping the double single quotes ('') is how to mssql escapes a single quote so the query would be


$query = "select * from users where lastname like 'o''brien';';

[djr33]

By "parametrized queries" you mean that you have a list of possible queries or parts of queries and the user's input selects which one to use, but there are no custom fields? That should be entirely secure.
(If one of your terms happens to be ;DROP DATABASE, then you'd need to fix that ;).)


Note that in the syntax of your code above you are mixing double and single quotes. I believe the last single quote should be double instead.

MySQL does that same method of escaping single quotes. You just never see it because mysql_real_escape...() does it for you.