Using mysql_query if i try to force more data than a field can have the
data is truncated yet no error is throw at all.
Is there a way round this ?
Cheers
Andy
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--_62152415-b158-49b4-85a0-4a63b6d374a7_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
> Using mysql_query if i try to force more data than a field can have the=20
> data is truncated yet no error is throw at all.
> Is there a way round this ?
> Cheers
>=20
> Andy
This isn't exactly what you want to hear, but how about validating your inp=
ut before submitting a query?
____________________________________________________________ _____
Boo!=A0Scare away worms, viruses and so much more! Try Windows Live OneCare=
!
http://onecare.live.com/standard/en-us/purchase/trial.aspx?s _cid=3Dwl_hotma=
ilnews=
--_62152415-b158-49b4-85a0-4a63b6d374a7_--
Hiya
I could check the length of the field against the entry data and
javascript myself out of trouble but i was more worried that there is no
error or message when mysql clearly returns one saying i've truncated
this yet php ignores it completely. It should fail or know about the
truncation at least !
Cheers for your reply though :-)
Andy
Instruct ICC wrote:
>> Using mysql_query if i try to force more data than a field can have the
>> data is truncated yet no error is throw at all.
>> Is there a way round this ?
>> Cheers
>>
>> Andy
>>
>
> This isn't exactly what you want to hear, but how about validating your input before submitting a query?
>
> ____________________________________________________________ _____
> Boo! Scare away worms, viruses and so much more! Try Windows Live OneCare!
> http://onecare.live.com/standard/en-us/purchase/trial.aspx?s _cid=wl_hotmailnews
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
You may want to check the my.ini setting for the table type you are
using and see if there is a setting in there that needs to be enabled.=
=20
I thought I read once that truncation may happen silently depending on
the my.ini setting.
Just a thought.
Ken Vandegrift
Shari's Management Corporation
Web Developer/Administrator
(Direct) 503-605-4132
kvandegrift@sharis.com
-----Original Message-----
From: Andrew Blake [mailto:a.blake@har.mrc.ac.uk]=20
Sent: Thursday, November 08, 2007 7:51 AM
To: Instruct ICC
Cc: php-db@lists.php.net
Subject: Re: [PHP-DB] mysql data truncation does not cause an error to
be thrown
Hiya
I could check the length of the field against the entry data and=20
javascript myself out of trouble but i was more worried that there is=
no
error or message when mysql clearly returns one saying i've truncated=20
this yet php ignores it completely. It should fail or know about the=20
truncation at least !
Cheers for your reply though :-)
Andy
Instruct ICC wrote:
>> Using mysql_query if i try to force more data than a field can have
the=20
>> data is truncated yet no error is throw at all.
>> Is there a way round this ?
>> Cheers
>>
>> Andy
>> =20
>
> This isn't exactly what you want to hear, but how about validating
your input before submitting a query?
>
> ____________________________________________________________ _____
> Boo! Scare away worms, viruses and so much more! Try Windows Live
OneCare!
>
http://onecare.live.com/standard/en-us/purchase/trial.aspx?s _cid=3Dwl_ho=
tm
ailnews
> =20
--=20
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--_112b7257-b93e-44da-800d-b7262623b497_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I agree. And maybe there is an error reporting level that can be set in my=
sql.
But you should also start sanitizing user input. And do it on the server s=
ide (even if you do some on the client side).
Maybe your form's action script could be sent to directly and your javascri=
pt validation sidestepped.
> Date: Thu, 8 Nov 2007 15:50:38 +0000
> From: a.blake@har.mrc.ac.uk
> To: instructicc@hotmail.com
> CC: php-db@lists.php.net
> Subject: Re: [PHP-DB] mysql data truncation does not cause an error to be=
thrown
>=20
> Hiya
> I could check the length of the field against the entry data and=20
> javascript myself out of trouble but i was more worried that there is no=
=20
> error or message when mysql clearly returns one saying i've truncated=20
> this yet php ignores it completely. It should fail or know about the=20
> truncation at least !
> Cheers for your reply though :-)
>=20
> Andy
>=20
> Instruct ICC wrote:
> >> Using mysql_query if i try to force more data than a field can have th=
e=20
> >> data is truncated yet no error is throw at all.
> >> Is there a way round this ?
> >> Cheers
> >>
> >> Andy
> >> =20
> >
> > This isn't exactly what you want to hear, but how about validating your=
input before submitting a query?
> >
> > ____________________________________________________________ _____
> > Boo! Scare away worms, viruses and so much more! Try Windows Live OneCa=
re!
> > http://onecare.live.com/standard/en-us/purchase/trial.aspx?s _cid=3Dwl_h=
otmailnews
> > =20
>=20
> --=20
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>=20
____________________________________________________________ _____
Help yourself to FREE treats served up daily at the Messenger Caf=E9. Stop =
by today.
http://www.cafemessenger.com/info/info_sweetstuff2.html?ocid =3DTXT_TAGLM_Oc=
tWLtagline=
--_112b7257-b93e-44da-800d-b7262623b497_--
I figured it out
it was the mysql install not php :-)
cheers for your help though :-)
Vandegrift, Ken wrote:
> You may want to check the my.ini setting for the table type you are
> using and see if there is a setting in there that needs to be enabled.
>
> I thought I read once that truncation may happen silently depending on
> the my.ini setting.
>
> Just a thought.
>
> Ken Vandegrift
> Shari's Management Corporation
> Web Developer/Administrator
> (Direct) 503-605-4132
> kvandegrift@sharis.com
> -----Original Message-----
> From: Andrew Blake [mailto:a.blake@har.mrc.ac.uk]
> Sent: Thursday, November 08, 2007 7:51 AM
> To: Instruct ICC
> Cc: php-db@lists.php.net
> Subject: Re: [PHP-DB] mysql data truncation does not cause an error to
> be thrown
>
> Hiya
> I could check the length of the field against the entry data and
> javascript myself out of trouble but i was more worried that there is no
>
> error or message when mysql clearly returns one saying i've truncated
> this yet php ignores it completely. It should fail or know about the
> truncation at least !
> Cheers for your reply though :-)
>
> Andy
>
> Instruct ICC wrote:
>
>>> Using mysql_query if i try to force more data than a field can have
>>>
> the
>
>>> data is truncated yet no error is throw at all.
>>> Is there a way round this ?
>>> Cheers
>>>
>>> Andy
>>>
>>>
>> This isn't exactly what you want to hear, but how about validating
>>
> your input before submitting a query?
>
>> ____________________________________________________________ _____
>> Boo! Scare away worms, viruses and so much more! Try Windows Live
>>
> OneCare!
>
> http://onecare.live.com/standard/en-us/purchase/trial.aspx?s _cid=wl_hotm
> ailnews
>
>>
>>
>
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--_34f3226a-5cc3-4df0-8903-4b37dae55676_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Maybe tell the list the exact solution?
File/variable/setting?
Cheers.
> I figured it out
>=20
> it was the mysql install not php :-)
> cheers for your help though :-)
>=20
> Vandegrift, Ken wrote:
> > You may want to check the my.ini setting for the table type you are
> > using and see if there is a setting in there that needs to be enabled. =
=20
> >
> > I thought I read once that truncation may happen silently depending on
> > the my.ini setting.
____________________________________________________________ _____
Peek-a-boo FREE Tricks & Treats for You!
http://www.reallivemoms.com?ocid=3DTXT_TAGHM&loc=3Dus=
--_34f3226a-5cc3-4df0-8903-4b37dae55676_--
Hello
It seems that the normal mysql_query doesn't error when an sql command
forces the server to truncate the data. The mysql server executes the
sql and provides a warning but it still executes the sql so allowing php
to ignore the fact that there is a warning. You can run another sql
command "SHOW COUNT(*) WARNINGS" after your insert command to check if
there is a warning and roll back the changes - similar to the java mysql
interface. I haven't used mysqli and perhaps this is addressed in that ?
Using sqlyog (3rd party mysql gui) to run the sql also provides no
warning message but running it on the command line provides the "1 row
affected, 1 warning message" With data validation and check this data
too large situation should never happen but surely php should pay
attention to the warnings too ?
code to reproduce the problem -->
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password'; *
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error
connecting to mysql');*
$dbname = 'andy';
*mysql_select_db($dbname);*
insertquery="insert into test values('012345678901234567891')";
$results= mysql_query($insertquery) or die('Error, insert query failed');
print_r($results);
echo "
".mysql_errno($db) . ": " . mysql_error($db) . "
".
mysql_info($db) ;
?>
[SQL]
create database if not exists `andy`;
USE `andy`;
/*Table structure for table `test` */
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`test` varchar(20) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;
[/SQL]
I understand that there are ways around this but php should really
automatically notify of the warning.
Maybe i'm missing something obvious......
Cheers
Andy
Andrew Blake wrote:
> I figured it out
>
> it was the mysql install not php :-)
> cheers for your help though :-)
>
> Vandegrift, Ken wrote:
>> You may want to check the my.ini setting for the table type you are
>> using and see if there is a setting in there that needs to be enabled.
>> I thought I read once that truncation may happen silently depending on
>> the my.ini setting.
>>
>> Just a thought.
>>
>> Ken Vandegrift
>> Shari's Management Corporation
>> Web Developer/Administrator
>> (Direct) 503-605-4132
>> kvandegrift@sharis.com
>> -----Original Message-----
>> From: Andrew Blake [mailto:a.blake@har.mrc.ac.uk] Sent: Thursday,
>> November 08, 2007 7:51 AM
>> To: Instruct ICC
>> Cc: php-db@lists.php.net
>> Subject: Re: [PHP-DB] mysql data truncation does not cause an error to
>> be thrown
>>
>> Hiya
>> I could check the length of the field against the entry data and
>> javascript myself out of trouble but i was more worried that there is no
>>
>> error or message when mysql clearly returns one saying i've truncated
>> this yet php ignores it completely. It should fail or know about the
>> truncation at least !
>> Cheers for your reply though :-)
>>
>> Andy
>>
>> Instruct ICC wrote:
>>
>>>> Using mysql_query if i try to force more data than a field can have
>>>>
>> the
>>>> data is truncated yet no error is throw at all.
>>>> Is there a way round this ?
>>>> Cheers
>>>>
>>>> Andy
>>>>
>>> This isn't exactly what you want to hear, but how about validating
>>>
>> your input before submitting a query?
>>
>>> ____________________________________________________________ _____
>>> Boo! Scare away worms, viruses and so much more! Try Windows Live
>>>
>> OneCare!
>>
>> http://onecare.live.com/standard/en-us/purchase/trial.aspx?s _cid=wl_hotm
>> ailnews
>>
>>>
>>
>>
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Should i say badly written code to reproduce the problem
here is the code again with the errors
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$db= mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error
connecting to mysql');
$dbname = 'andy';
mysql_select_db($dbname);
$insertquery="insert into test values('012345678901234567891')";
$results= mysql_query($insertquery) or die('Error, insert query failed');
print_r($results);
echo "
".mysql_errno($db) . ": " . mysql_error($db) . "
".
mysql_info($db) ;
?>
Andrew Blake wrote:
> Hello
>
> It seems that the normal mysql_query doesn't error when an sql command
> forces the server to truncate the data. The mysql server executes the
> sql and provides a warning but it still executes the sql so allowing
> php to ignore the fact that there is a warning. You can run another
> sql command "SHOW COUNT(*) WARNINGS" after your insert command to
> check if there is a warning and roll back the changes - similar to the
> java mysql interface. I haven't used mysqli and perhaps this is
> addressed in that ? Using sqlyog (3rd party mysql gui) to run the sql
> also provides no warning message but running it on the command line
> provides the "1 row affected, 1 warning message" With data validation
> and check this data too large situation should never happen but surely
> php should pay attention to the warnings too ?
>
> code to reproduce the problem -->
>
>
> $dbhost = 'localhost';
> $dbuser = 'root';
> $dbpass = 'password'; *
> $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error
> connecting to mysql');*
> $dbname = 'andy';
> *mysql_select_db($dbname);*
> insertquery="insert into test values('012345678901234567891')";
> $results= mysql_query($insertquery) or die('Error, insert query failed');
> print_r($results);
> echo "
".mysql_errno($db) . ": " . mysql_error($db) . "
".
> mysql_info($db) ;
> ?>
>
> [SQL]
> create database if not exists `andy`;
> USE `andy`;
> /*Table structure for table `test` */
> DROP TABLE IF EXISTS `test`;
> CREATE TABLE `test` (
> `test` varchar(20) default NULL
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;
> [/SQL]
>
> I understand that there are ways around this but php should really
> automatically notify of the warning.
> Maybe i'm missing something obvious......
>
> Cheers
>
> Andy
> Andrew Blake wrote:
>> I figured it out
>>
>> it was the mysql install not php :-)
>> cheers for your help though :-)
>>
>> Vandegrift, Ken wrote:
>>> You may want to check the my.ini setting for the table type you are
>>> using and see if there is a setting in there that needs to be
>>> enabled. I thought I read once that truncation may happen silently
>>> depending on
>>> the my.ini setting.
>>>
>>> Just a thought.
>>>
>>> Ken Vandegrift
>>> Shari's Management Corporation
>>> Web Developer/Administrator
>>> (Direct) 503-605-4132
>>> kvandegrift@sharis.com
>>> -----Original Message-----
>>> From: Andrew Blake [mailto:a.blake@har.mrc.ac.uk] Sent: Thursday,
>>> November 08, 2007 7:51 AM
>>> To: Instruct ICC
>>> Cc: php-db@lists.php.net
>>> Subject: Re: [PHP-DB] mysql data truncation does not cause an error to
>>> be thrown
>>>
>>> Hiya
>>> I could check the length of the field against the entry data and
>>> javascript myself out of trouble but i was more worried that there
>>> is no
>>>
>>> error or message when mysql clearly returns one saying i've
>>> truncated this yet php ignores it completely. It should fail or know
>>> about the truncation at least !
>>> Cheers for your reply though :-)
>>>
>>> Andy
>>>
>>> Instruct ICC wrote:
>>>
>>>>> Using mysql_query if i try to force more data than a field can have
>>>>>
>>> the
>>>>> data is truncated yet no error is throw at all.
>>>>> Is there a way round this ?
>>>>> Cheers
>>>>>
>>>>> Andy
>>>>>
>>>> This isn't exactly what you want to hear, but how about validating
>>>>
>>> your input before submitting a query?
>>>
>>>> ____________________________________________________________ _____
>>>> Boo! Scare away worms, viruses and so much more! Try Windows Live
>>>>
>>> OneCare!
>>>
>>> http://onecare.live.com/standard/en-us/purchase/trial.aspx?s _cid=wl_hotm
>>>
>>> ailnews
>>>
>>>>
>>>
>>>
>>
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
On Nov 9, 2007 8:34 PM, Andrew Blake
> Hello
>
> It seems that the normal mysql_query doesn't error when an sql command
> forces the server to truncate the data. The mysql server executes the
> sql and provides a warning but it still executes the sql so allowing php
> to ignore the fact that there is a warning. You can run another sql
> command "SHOW COUNT(*) WARNINGS" after your insert command to check if
> there is a warning and roll back the changes - similar to the java mysql
> interface. I haven't used mysqli and perhaps this is addressed in that ?
I doubt it.
> Using sqlyog (3rd party mysql gui) to run the sql also provides no
> warning message but running it on the command line provides the "1 row
> affected, 1 warning message" With data validation and check this data
> too large situation should never happen but surely php should pay
> attention to the warnings too ?
It's not a php problem. Mysql should throw proper errors instead of a 'warning'.
http://dev.mysql.com/doc/refman/4.1/en/blob.html
If you assign a value to a BLOB or TEXT column that exceeds the data
type's maximum length, the value is truncated to fit and a warning is
generated.
Same for char/varchar & binary fields
(http://dev.mysql.com/doc/refman/4.1/en/binary-varbinary.htm l and
http://dev.mysql.com/doc/refman/4.1/en/char.html).
You will get the same "problem" in other languages I'm sure (try it in
python, perl, ruby).
--
Postgresql & php tutorials
http://www.designmagick.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Hiya
This seems to be one thing java has over php (perl etc) as it does know
about the warning and acts approperately. This should be an option to
ignore or not ignore warnings within php to prevent errorless truncation
of data.
Cheers
Andy
chris smith wrote:
> On Nov 9, 2007 8:34 PM, Andrew Blake
>
>> Hello
>>
>> It seems that the normal mysql_query doesn't error when an sql command
>> forces the server to truncate the data. The mysql server executes the
>> sql and provides a warning but it still executes the sql so allowing php
>> to ignore the fact that there is a warning. You can run another sql
>> command "SHOW COUNT(*) WARNINGS" after your insert command to check if
>> there is a warning and roll back the changes - similar to the java mysql
>> interface. I haven't used mysqli and perhaps this is addressed in that ?
>>
>
> I doubt it.
>
>
>> Using sqlyog (3rd party mysql gui) to run the sql also provides no
>> warning message but running it on the command line provides the "1 row
>> affected, 1 warning message" With data validation and check this data
>> too large situation should never happen but surely php should pay
>> attention to the warnings too ?
>>
>
> It's not a php problem. Mysql should throw proper errors instead of a 'warning'.
>
> http://dev.mysql.com/doc/refman/4.1/en/blob.html
> If you assign a value to a BLOB or TEXT column that exceeds the data
> type's maximum length, the value is truncated to fit and a warning is
> generated.
>
> Same for char/varchar & binary fields
> (http://dev.mysql.com/doc/refman/4.1/en/binary-varbinary.htm l and
> http://dev.mysql.com/doc/refman/4.1/en/char.html).
>
> You will get the same "problem" in other languages I'm sure (try it in
> python, perl, ruby).
>
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php