MSSQL injection?

Discussions of secure PHP coding. Security in software is important, so don't be afraid to ask. And when answering: be anal. Nitpick. No security vulnerability is too small.

Moderator: General Moderators

Post Reply
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

MSSQL injection?

Post 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
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Re: MSSQL injection?

Post 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).
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Re: MSSQL injection?

Post 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?
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: MSSQL injection?

Post 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?
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Re: MSSQL injection?

Post 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.
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: MSSQL injection?

Post 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.
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Re: MSSQL injection?

Post 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.
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Re: MSSQL injection?

Post 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.
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Re: MSSQL injection?

Post by kaisellgren »

Mordred wrote:MSSQL does SELECT TOP n FROM ..., not LIMIT.
Oh yeah my bad. Btw, it is T-SQL, not MSSQL. :lol:
Post Reply