Page 1 of 1

MSSQL injection?

Posted: Fri Feb 27, 2009 9:50 am
by alex.barylski
The code base I work on uses MSSQL.

I just had a breif discussion with the lead developer who explained he uses the following funciton to escape data before submittion to the DB:

Code: Select all

 
function mssql_escape($str){    return str_replace('"','""',str_replace("'","''",TRIM($str)));  }
function mssql_unescape($str){  return str_replace('""','"',str_replace("''","'",$str));    }
 
function mssql2js($str){    
  return str_replace(''.chr(10).'','\n',str_replace(''.chr(13).'','\r',str_replace('"','\"',str_replace("'","\'",TRIM($str)))));  
}
 
function mssql_txt_escape($str) {
  return str_replace("'","''",str_replace("<","<",str_replace(">",">",str_replace("`","'",str_replace(CHR_UNI(145),"'",str_replace(CHR_UNI(146),"'",str_replace(CHR_UNI(147),'"',str_replace(CHR_UNI(148),'"',$str))))))));}
function mssql_txt_unescape($str){return str_replace("''","'",$str);    
}
 
It appears all he is doing is replacing single quote with double quote and double quotes with double-double quotes to prevent the SQL from breaking. Is this enough to prevent SQLi exploits?

Were using ODBC to connect to a Windows MSSQL from a Ubuntu box

Re: MSSQL injection?

Posted: Fri Feb 27, 2009 10:17 am
by kaisellgren
If I recall correctly, NULL bytes may break the query. If they do, then you need to get rid of them. I do not have SQL Server installed at the moment, so you have to test yourself.

Also, be careful with meta characters such as % and _. You cannot escape them with quotes, they must be escaped with brackets. For instance: [%].

More over, you may specify your own escape character:

Code: Select all

ESCAPE ‘\’
Then you could just do addcslashes('%_','\\',...) to escape the meta characters. (Note, that also affects the other parts of the queries).

Re: MSSQL injection?

Posted: Fri Feb 27, 2009 5:24 pm
by Mordred
If they forget to add quotes to a value in the query, or if they dynamically build other parts of the query (SELECT TOP $n blabla, or ORDER BY $s, stuff like this), quote-less SQLi is still possible.
Also, why do they need the unescapes?

Re: MSSQL injection?

Posted: Fri Feb 27, 2009 6:27 pm
by alex.barylski
Also, why do they need the unescapes?
I have no idea...the guy who designed the system thought what was quoted needed unquoting. :P
If they forget to add quotes to a value in the query, or if they dynamically build other parts of the query (SELECT TOP $n blabla, or ORDER BY $s, stuff like this), quote-less SQLi is still possible.
So it's secure because MSSQL uses prepared statements, right? So parameterized exploits are not likely/possible? But clauses, etc could be?

Re: MSSQL injection?

Posted: Fri Feb 27, 2009 6:37 pm
by kaisellgren
Prepared statements do not automatically prevent SQL injections.

For instance, think about:

Code: Select all

SELECT * FROM x LIMIT ?
Where ? is the place holder. Regardless of using prepared statements or not, we do have to filter the data first.

Re: MSSQL injection?

Posted: Fri Feb 27, 2009 8:20 pm
by alex.barylski
From what I understand (I have no seen any eveidence prepared statements are being used) field values are automagically escaped and converted into the appropriate type, so if you assign a string to a numeric field it's converted to an integer (0) and the query is then evaluated -- at least this is what appears to be happening.

Re: MSSQL injection?

Posted: Sat Feb 28, 2009 8:07 am
by kaisellgren
PCSpectra wrote:From what I understand (I have no seen any eveidence prepared statements are being used) field values are automagically escaped and converted into the appropriate type, so if you assign a string to a numeric field it's converted to an integer (0) and the query is then evaluated -- at least this is what appears to be happening.
And what if it is supposed to be a string, is it still filtered?

Code: Select all

ORDER BY '$orderby'
Even if you escape that, it is not yet sufficient since the attacker can put anything on it. He can sort the list by any column name, e.g. by identifiers, usernames or something else and this way gain information about the database. It is essential to filter the data, and make sure only expected string formats pass it.

Re: MSSQL injection?

Posted: Sat Feb 28, 2009 11:30 am
by Mordred
kaisellgren wrote:

Code: Select all

SELECT * FROM x LIMIT ?
MSSQL does SELECT TOP n FROM ..., not LIMIT.

Anyway, I don't have much experience with MSSQL either, but IIRC NULLs could be used to truncate a query, so Hockey, you better try it.

Re: MSSQL injection?

Posted: Sat Feb 28, 2009 11:33 am
by kaisellgren
Mordred wrote:MSSQL does SELECT TOP n FROM ..., not LIMIT.
Oh yeah my bad. Btw, it is T-SQL, not MSSQL. :lol: