Important article: SQL Column Truncation

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

Important article: SQL Column Truncation

Post by Mordred »

http://www.suspekt.org/2008/08/18/mysql ... abilities/

Another point in favour of generating SQL statements.
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Re: Important article: SQL Column Truncation

Post by Oren »

Great, thanks :drunk:
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Re: Important article: SQL Column Truncation

Post by kaisellgren »

Old news, but as you brought it up, I'll add my comments here. :)

Best practise is to truncate data by yourself whenever you insert or update or elseway modify the contents of your db. This is, however, my opinion and I haven't really thought about this any more deeply. I always program apps that will be used on various servers, therefore, it must be compatible, working and secure.

I'll write quickly two sample truncation functions (are not tested):

Code: Select all

function truncate_basic($string,$length)
 {
  return substr($string,0,$length);
 }
UTF8 compatible truncation:

Code: Select all

function truncate($string,$length = 255)
 {
  if (strlen($string) <= $length)
   return $string;
 
  if ((ord($string[$length]) <= 0x7F) || (ord($string[$length]) >= 0xC0))
   return substr($string,0,$length);
 
  while ($length > 0)
   {
    $length--;
    if (ord($string[$length]) <= 0x7F || ord($string[$length]) >= 0xC0)
     break;
   }
  return substr($string,0,$length);
 }
The ladder checks if the last byte is a utf8 continuation byte, if it is, loop back until we reach a good point, if it is not, then substr.

And if you are using MySQL, you could disable te truncation on MySQL's side to stop double truncation (unnecessary, however, extra security):

Code: Select all

if (mysqli_get_server_version($this -> connection_id) >= 50002) // Strict modes as of 5.0.2, same for session/global
       {
        $result = mysqli_query($this -> connection_id,'SELECT @@session.sql_mode AS mode;');
                $array = mysqli_fetch_assoc($result);
                mysqli_free_result($result);
                $modes = array_map('trim',explode(',',$array['mode']));
 
        if (!in_array('TRADITIONAL',$modes))
         {
          if (!in_array('STRICT_ALL_TABLES',$modes))
           $modes[] = 'STRICT_ALL_TABLES';
          if (!in_array('STRICT_TRANS_TABLES',$modes))
           $modes[] = 'STRICT_TRANS_TABLES';
         }
 
                $mode = implode(',',$modes);
                mysqli_query($this -> connection_id,"SET SESSION sql_mode='$mode';");
       }
You have to play around with the ladder code in terms of vars and such as it is taken from my project which uses db abstraction layer and is oop.
Post Reply