http://www.suspekt.org/2008/08/18/mysql ... abilities/
Another point in favour of generating SQL statements.
Important article: SQL Column Truncation
Moderator: General Moderators
Re: Important article: SQL Column Truncation
Great, thanks 
- kaisellgren
- DevNet Resident
- Posts: 1675
- Joined: Sat Jan 07, 2006 5:52 am
- Location: Lahti, Finland.
Re: Important article: SQL Column Truncation
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):
UTF8 compatible truncation:
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):
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.
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);
}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);
}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';");
}