Page 1 of 1

Important article: SQL Column Truncation

Posted: Sat Sep 13, 2008 7:25 am
by Mordred
http://www.suspekt.org/2008/08/18/mysql ... abilities/

Another point in favour of generating SQL statements.

Re: Important article: SQL Column Truncation

Posted: Sun Sep 14, 2008 3:57 am
by Oren
Great, thanks :drunk:

Re: Important article: SQL Column Truncation

Posted: Tue Sep 16, 2008 7:27 am
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.