Bug #73264 [Com]: pg_convert rejects valid values for several data types

Edit report at https://bugs.php.net/bug.php?id=73264&edit=1 ID: 73264 Comment by: php at willian dot resende dot nom dot br Reported by: hans at xs4all dot nl Summary: pg_convert rejects valid values for several data types Status: Open Type: Bug Package: PostgreSQL related Operating System: Linux PHP Version: Irrelevant Block user comment: N Private report: N New Comment: Owner's database can set DATESTYLE like 'SQL, DMY' on postgresql.conf or (ALTER DATABASE dbname SET DateStyle = 'SQL, DMY') or (SET datestyle = 'SQL, DMY') per session. So, the input string can be '31/12/2016' on setted european's date style. The regular expression must be aware of datestyle to decide parse string. Hint: SHOW DATESTYLE; Previous Comments: ------------------------------------------------------------------------ [2016-10-07 11:32:07] hans at xs4all dot nl Description: ------------ The regular expressions used in pg_convert for several data types reject many different valid values. As the test script below demonstrates: - For cidr and inet only IPv6 addresses without network mask are accepted, so networks and IPv4 are not supported. - For date and time (and timestamp) only a subset of ISO 8601 formatted strings are accepted. - For intervals only verbose syntax is accepted. The changelog of the function (http://php.net/pg_convert) states that 'unknown/unsupported data types are escaped without validation'. Maybe a better approach is to not validate these data types too and just escape them. Test script: --------------- $db = pg_connect('dbname=foo'); pg_query($db, 'create table test (_inet inet, _cidr cidr, _date date, _time time, _interval interval)'); error_reporting(0); print_r(pg_convert($db, 'test', ['_inet'=>'192.168.100.128/25'])); print_r(pg_convert($db, 'test', ['_inet'=>'192.168/24'])); print_r(pg_convert($db, 'test', ['_inet'=>'192.168/25'])); print_r(pg_convert($db, 'test', ['_inet'=>'192.168.1'])); print_r(pg_convert($db, 'test', ['_inet'=>'192168'])); print_r(pg_convert($db, 'test', ['_inet'=>'128.1'])); print_r(pg_convert($db, 'test', ['_inet'=>'128'])); print_r(pg_convert($db, 'test', ['_inet'=>'128.1.2'])); print_r(pg_convert($db, 'test', ['_inet'=>'10.1.2'])); print_r(pg_convert($db, 'test', ['_inet'=>'10.1'])); print_r(pg_convert($db, 'test', ['_inet'=>'10'])); print_r(pg_convert($db, 'test', ['_inet'=>'10.1.2.3/32'])); print_r(pg_convert($db, 'test', ['_inet'=>'2001:4f8:3:ba::/64'])); print_r(pg_convert($db, 'test', ['_inet'=>'2001:4f8:3:ba:2e0:81ff:fe22:d1f1/128'])); print_r(pg_convert($db, 'test', ['_inet'=>'2001:4f8:3:ba:2e0:81ff:fe22:d1f1'])); print_r(pg_convert($db, 'test', ['_cidr'=>'192.168.100.128/25'])); print_r(pg_convert($db, 'test', ['_cidr'=>'192.168/24'])); print_r(pg_convert($db, 'test', ['_cidr'=>'192.168/25'])); print_r(pg_convert($db, 'test', ['_cidr'=>'192.168.1'])); print_r(pg_convert($db, 'test', ['_cidr'=>'192168'])); print_r(pg_convert($db, 'test', ['_cidr'=>'128.1'])); print_r(pg_convert($db, 'test', ['_cidr'=>'128'])); print_r(pg_convert($db, 'test', ['_cidr'=>'128.1.2'])); print_r(pg_convert($db, 'test', ['_cidr'=>'10.1.2'])); print_r(pg_convert($db, 'test', ['_cidr'=>'10.1'])); print_r(pg_convert($db, 'test', ['_cidr'=>'10'])); print_r(pg_convert($db, 'test', ['_cidr'=>'10.1.2.3/32'])); print_r(pg_convert($db, 'test', ['_cidr'=>'2001:4f8:3:ba::/64'])); print_r(pg_convert($db, 'test', ['_cidr'=>'2001:4f8:3:ba:2e0:81ff:fe22:d1f1/128'])); print_r(pg_convert($db, 'test', ['_cidr'=>'2001:4f8:3:ba:2e0:81ff:fe22:d1f1'])); print_r(pg_convert($db, 'test', ['_date'=>'1999-01-08'])); print_r(pg_convert($db, 'test', ['_date'=>'January 8, 1999'])); print_r(pg_convert($db, 'test', ['_date'=>'1/8/1999'])); print_r(pg_convert($db, 'test', ['_date'=>'1/18/1999'])); print_r(pg_convert($db, 'test', ['_date'=>'01/02/03'])); print_r(pg_convert($db, 'test', ['_date'=>'1999-Jan-08'])); print_r(pg_convert($db, 'test', ['_date'=>'Jan-08-1999'])); print_r(pg_convert($db, 'test', ['_date'=>'08-Jan-1999'])); print_r(pg_convert($db, 'test', ['_date'=>'99-Jan-08'])); print_r(pg_convert($db, 'test', ['_date'=>'08-Jan-99'])); print_r(pg_convert($db, 'test', ['_date'=>'Jan-08-99'])); print_r(pg_convert($db, 'test', ['_date'=>'19990108'])); print_r(pg_convert($db, 'test', ['_date'=>'990108'])); print_r(pg_convert($db, 'test', ['_date'=>'1999008'])); print_r(pg_convert($db, 'test', ['_date'=>'J2451187'])); print_r(pg_convert($db, 'test', ['_date'=>'January 8, 99 BC'])); print_r(pg_convert($db, 'test', ['_date'=>'epoch'])); print_r(pg_convert($db, 'test', ['_date'=>'infinity'])); print_r(pg_convert($db, 'test', ['_date'=>'-infinity'])); print_r(pg_convert($db, 'test', ['_date'=>'now'])); print_r(pg_convert($db, 'test', ['_date'=>'today'])); print_r(pg_convert($db, 'test', ['_date'=>'tomorrow'])); print_r(pg_convert($db, 'test', ['_date'=>'yesterday'])); print_r(pg_convert($db, 'test', ['_time'=>'04:05:06.789'])); print_r(pg_convert($db, 'test', ['_time'=>'04:05:06'])); print_r(pg_convert($db, 'test', ['_time'=>'04:05'])); print_r(pg_convert($db, 'test', ['_time'=>'40506'])); print_r(pg_convert($db, 'test', ['_time'=>'04:05 AM'])); print_r(pg_convert($db, 'test', ['_time'=>'04:05 PM'])); print_r(pg_convert($db, 'test', ['_time'=>'04:05:06.789-8'])); print_r(pg_convert($db, 'test', ['_time'=>'04:05:06-08:00'])); print_r(pg_convert($db, 'test', ['_time'=>'04:05-08:00'])); print_r(pg_convert($db, 'test', ['_time'=>'040506-08'])); print_r(pg_convert($db, 'test', ['_time'=>'04:05:06 PST'])); print_r(pg_convert($db, 'test', ['_time'=>'2003-04-12 04:05:06 America/New_York'])); print_r(pg_convert($db, 'test', ['_time'=>'allballs'])); print_r(pg_convert($db, 'test', ['_interval'=>'1-2'])); print_r(pg_convert($db, 'test', ['_interval'=>'3 4:05:06'])); print_r(pg_convert($db, 'test', ['_interval'=>'04:5:6'])); print_r(pg_convert($db, 'test', ['_interval'=>'1 year 2 months 3 days 4 hours 5 minutes 6 seconds'])); print_r(pg_convert($db, 'test', ['_interval'=>'1 year 2 mons 3 days 4 hours 5 mins 6 secs'])); print_r(pg_convert($db, 'test', ['_interval'=>'P1Y2M3DT4H5M6S'])); print_r(pg_convert($db, 'test', ['_interval'=>'P0001-02-03T04:05:06'])); Expected result: ---------------- All values submitted to pg_convert are valid, so all return values should be printed. Actual result: -------------- Array ( ["_inet"] => E'2001:4f8:3:ba:2e0:81ff:fe22:d1f1' ) Array ( ["_cidr"] => E'2001:4f8:3:ba:2e0:81ff:fe22:d1f1' ) Array ( ["_date"] => E'1999-01-08' ) Array ( ["_time"] => E'04:05:06' ) Array ( ["_time"] => E'04:05' ) Array ( ["_interval"] => E'1 year 2 months 3 days 4 hours 5 minutes 6 seconds' ) ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=73264&edit=1