search

PostgreSQL-For-Wordpress / postgresql-for-wordpress

A maintained fork of https://wordpress.org/plugins/postgresql-for-wordpress/
GNU General Public License v2.0
208 stars 67 forks source link

SQL tables not created because of usage of reserved keywords in column names #77

Closed hahnn closed 4 months ago

hahnn commented 7 months ago

Hi,

Trying to re-enable the Complianz GDPR plugin, I discovered it can happen some plugins are using PostgreSQL keywords as column names, thus creating an error.

For example, I've the case when the SQL table below is trying to be created:

2023-11-26 11:19:24.456 CET [pid=2340706]: [sid=65631bab.23b762/tid=0] client=,user=,db=,app=[inconnu] ERREUR:  erreur de syntaxe sur ou près de « default » au caractère 178
2023-11-26 11:19:24.456 CET [pid=2340706]: [sid=65631bab.23b762/tid=0] client=,user=,db=,app=[inconnu] INSTRUCTION :  CREATE TABLE wp_cmplz_cookiebanners (
                     "ID" int NOT NULL DEFAULT nextval('wp_cmplz_cookiebanners_seq'::text),
                     banner_version int NOT NULL,
                     default int NOT NULL,
                     archived int NOT NULL,
                     title text NOT NULL,
                    position text NOT NULL,
                    theme text NOT NULL,
                    checkbox_style text NOT NULL,
                    use_logo text NOT NULL,
                    logo_attachment_id text NOT NULL,
                                close_button text NOT NULL,
                    revoke text NOT NULL,
                    manage_consent_options text NOT NULL,
                    header text NOT NULL,
                    dismiss text NOT NULL,
                    save_preferences text NOT NULL,
                    view_preferences text NOT NULL,
                    category_functional text NOT NULL,
                    category_all text NOT NULL,
                    category_stats text NOT NULL,
                    category_prefs text NOT NULL,
                    accept text NOT NULL,
                    message_optin text NOT NULL,
                    use_categories text NOT NULL,
                    disable_cookiebanner int NOT NULL,
                    banner_width int NOT NULL,
                    soft_cookiewall int NOT NULL,
                    dismiss_on_scroll int NOT NULL,
                    dismiss_on_timeout int NOT NULL,
                    dismiss_timeout text NOT NULL,
                    accept_informational text NOT NULL,
                    message_optout text NOT NULL,
                    use_custom_cookie_css text NOT NULL,
                    custom_css text NOT NULL,
                    statistics text NOT NULL,
                    functional_text text NOT NULL,
                    statistics_text text NOT NULL,
                    statistics_text_anonymous text NOT NULL,
                    preferences_text text NOT NULL,
                    marketing_text text NOT NULL,
                    colorpalette_background text NOT NULL,
                    colorpalette_text text NOT NULL,
                    colorpalette_toggles text NOT NULL,
                    colorpalette_border_radius text NOT NULL,
                    border_width text NOT NULL,
                    font_size text NOT NULL,
                    colorpalette_button_accept text NOT NULL,
                    colorpalette_button_deny text NOT NULL,
                    colorpalette_button_settings text NOT NULL,
                    buttons_border_radius text NOT NULL,
                    animation text NOT NULL,
                    use_box_shadow int NOT NULL,
                    header_footer_shadow int NOT NULL,
                    hide_preview int NOT NULL,
                    disable_width_correction int NOT NULL,
                    legal_documents int NOT NULL,
                      PRIMARY KEY  ( "ID" )
                    )  ;
        CREATE SEQUENCE wp_cmplz_cookiebanners_seq;

As you can see above, the third column has default for column name.

However, default is a reserved keyword that is used to automatically assign a default value to a column when data are inserted.

For such cases, the de facto way to use to avoid a PostgreSQL database error is to put the column name inside double quotes, so doing this:

CREATE TABLE wp_cmplz_cookiebanners (
                     "ID" int NOT NULL DEFAULT nextval('wp_cmplz_cookiebanners_seq'::text),
                     banner_version int NOT NULL,
                     "default" int NOT NULL,

instead of this:

CREATE TABLE wp_cmplz_cookiebanners (
                     "ID" int NOT NULL DEFAULT nextval('wp_cmplz_cookiebanners_seq'::text),
                     banner_version int NOT NULL,
                     default int NOT NULL,
hahnn commented 7 months ago

That's the case also for several plugins making use of reserved keywords in their CREATE TABLE statements. So we can produce a small list of column names that should be between double quotes to avoid issues:

timestamp date time default

mattbucci commented 6 months ago

I think the difficulty here will be that we can't just look for the word timestamp or default, so I think we need to parse the create table statement line by line using the comma as a way to split it.

However I've learned that it's a bit tricky because the commas can be in the lines, so we need to ignore commas which are between parentheses see for example

      CREATE TABLE wp_statistics_pages (
                page_id BIGINT(20) NOT NULL AUTO_INCREMENT,
                uri varchar(190) NOT NULL,
                type varchar(180) NOT NULL,
                date date NOT NULL,
                count int(11) NOT NULL,
                id int(11) NOT NULL,
                UNIQUE KEY date_2 (date,uri),
                KEY url (uri),
                KEY date (date),
                KEY id (id),
                KEY `uri` (`uri`,`count`,`id`),
                PRIMARY KEY (`page_id`)
            ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci
mattbucci commented 4 months ago

This is handled via this regex. it was one of the harder ones I've had to write for this project https://github.com/PostgreSQL-For-Wordpress/postgresql-for-wordpress/pull/93/commits/b7be1aa89a3486d2f80456e83d090e5ce273ec17

I will be very happy one day when we can replace these regexes with a parser https://regex101.com/r/1QZ5Oi/1

CommanderRedYT commented 4 weeks ago

@mattbucci I still have this error after b7be1aa.

wordpress-wordpress-1  | NOTICE: PHP message: WordPress database error ERROR:  syntax error at or near "default"
wordpress-wordpress-1  | LINE 5:              default int NOT NULL,
wordpress-wordpress-1  |                      ^ for query CREATE TABLE wp_cmplz_cookiebanners (
wordpress-wordpress-1  |              `ID` int(11) NOT NULL AUTO_INCREMENT,
wordpress-wordpress-1  |              `title` text NOT NULL,
wordpress-wordpress-1  |              `banner_version` int(11) NOT NULL,
wordpress-wordpress-1  |              `default` int(11) NOT NULL,
wordpress-wordpress-1  |             `position` text NOT NULL,
wordpress-wordpress-1  |             `checkbox_style` text NOT NULL,
wordpress-wordpress-1  |             `use_logo` text NOT NULL,
wordpress-wordpress-1  |             `logo_attachment_id` text NOT NULL,
wordpress-wordpress-1  |                        `close_button` text NOT NULL,
wordpress-wordpress-1  |             `revoke` text NOT NULL,
wordpress-wordpress-1  |             `manage_consent_options` text NOT NULL,
wordpress-wordpress-1  |             `header` text NOT NULL,
wordpress-wordpress-1  |             `dismiss` text NOT NULL,
wordpress-wordpress-1  |             `save_preferences` text NOT NULL,
wordpress-wordpress-1  |             `view_preferences` text NOT NULL,
wordpress-wordpress-1  |             `category_functional` text NOT NULL,
wordpress-wordpress-1  |             `category_all` text NOT NULL,
wordpress-wordpress-1  |             `category_stats` text NOT NULL,
wordpress-wordpress-1  |             `category_prefs` text NOT NULL,
wordpress-wordpress-1  |             `accept` text NOT NULL,
wordpress-wordpress-1  |             `message_optin` text NOT NULL,
wordpress-wordpress-1  |             `use_categories` text NOT NULL,
wordpress-wordpress-1  |             `disable_cookiebanner` int(11) NOT NULL,
wordpress-wordpress-1  |             `banner_width` int(11) NOT NULL,
wordpress-wordpress-1  |             `soft_cookiewall` int(11) NOT NULL,
wordpress-wordpress-1  |             `dismiss_on_scroll` int(11) NOT NULL,
wordpress-wordpress-1  |             `dismiss_on_timeout` int(11) NOT NULL,
wordpress-wordpress-1  |             `dismiss_timeout` text NOT NULL,
wordpress-wordpress-1  |             `accept_informational` text NOT NULL,
wordpress-wordpress-1  |             `message_optout` text NOT NULL,
wordpress-wordpress-1  |             `use_custom_cookie_css` text NOT NULL,
wordpress-wordpress-1  |             `custom_css` text NOT NULL,
wordpress-wordpress-1  |             `statistics` text NOT NULL,
wordpress-wordpress-1  |             `functional_text` text NOT NULL,
wordpress-wordpress-1  |             `statistics_text` text NOT NULL,
wordpress-wordpress-1  |             `statistics_text_anonymous` text NOT NULL,
wordpress-wordpress-1  |             `preferences_text` text NOT NULL,
wordpress-wordpress-1  |             `marketing_text` text NOT NULL,
wordpress-wordpress-1  |             `colorpalette_background` text NOT NULL,
wordpress-wordpress-1  |             `colorpalette_text` text NOT NULL,
wordpress-wordpress-1  |             `colorpalette_toggles` text NOT NULL,
wordpress-wordpress-1  |             `colorpalette_border_radius` text NOT NULL,
wordpress-wordpress-1  |             `border_width` text NOT NULL,
wordpress-wordpress-1  |             `font_size` text NOT NULL,
wordpress-wordpress-1  |             `colorpalette_button_accept` text NOT NULL,
wordpress-wordpress-1  |             `colorpalette_button_deny` text NOT NULL,
wordpress-wordpress-1  |             `colorpalette_button_settings` text NOT NULL,
wordpress-wordpress-1  |             `buttons_border_radius` text NOT NULL,
wordpress-wordpress-1  |             `animation` text NOT NULL,
wordpress-wordpress-1  |             `use_box_shadow` int(11) NOT NULL,
wordpress-wordpress-1  |             `header_footer_shadow` int(11) NOT NULL,
wordpress-wordpress-1  |             `hide_preview` int(11) NOT NULL,
wordpress-wordpress-1  |             `disable_width_correction` int(11) NOT NULL,
wordpress-wordpress-1  |             `legal_documents` int(11) NOT NULL,
wordpress-wordpress-1  |               PRIMARY KEY  (ID)
wordpress-wordpress-1  |             ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci made by require_once('wp-load.php'), require_once('wp-config.php'), require_once('wp-settings.php'), do_action('init'), WP_Hook->do_action, WP_Hook->apply_filters, cmplz_check_upgrade, do_action('cmplz_install_tables'), WP_Hook->do_action, WP_Hook->apply_filters, cmplz_install_cookiebanner_table, dbDelta
wordpress-wordpress-1  | NOTICE: PHP message: PHP Fatal error:  Uncaught Exception: Invalid or unsupported SQL statement. in /var/www/html/pg4wp/driver_pgsql_rewrite.php:26
wordpress-wordpress-1  | Stack trace:
wordpress-wordpress-1  | #0 /var/www/html/pg4wp/driver_pgsql_rewrite.php(40): createSQLRewriter('SHOW COLUMNS FR...')
wordpress-wordpress-1  | #1 /var/www/html/pg4wp/driver_pgsql.php(523): pg4wp_rewrite('SHOW COLUMNS FR...')
wordpress-wordpress-1  | #2 /var/www/html/pg4wp/core.php(34) : eval()'d code(2349): wpsqli_query(Object(PgSql\Connection), 'SHOW COLUMNS FR...')
wordpress-wordpress-1  | #3 /var/www/html/pg4wp/core.php(34) : eval()'d code(2263): wpdb2->_do_query('SHOW COLUMNS FR...')
wordpress-wordpress-1  | #4 /var/www/html/pg4wp/core.php(34) : eval()'d code(3146): wpdb2->query('SHOW COLUMNS FR...')
wordpress-wordpress-1  | #5 /var/www/html/wp-content/plugins/complianz-gdpr/cookiebanner/class-cookiebanner.php(88): wpdb2->get_results('SHOW COLUMNS FR...')
wordpress-wordpress-1  | #6 /var/www/html/wp-includes/class-wp-hook.php(324): cmplz_install_cookiebanner_table('')
wordpress-wordpress-1  | #7 /var/www/html/wp-includes/class-wp-hook.php(348): WP_Hook->apply_filters(NULL, Array)
wordpress-wordpress-1  | #8 /var/www/html/wp-includes/plugin.php(517): WP_Hook->do_action(Array)
wordpress-wordpress-1  | #9 /var/www/html/wp-content/plugins/complianz-gdpr/upgrade.php(27): do_action('cmplz_install_t...')
wordpress-wordpress-1  | #10 /var/www/html/wp-includes/class-wp-hook.php(324): cmplz_check_upgrade('')
wordpress-wordpress-1  | #11 /var/www/html/wp-includes/class-wp-hook.php(348): WP_Hook->apply_filters(NULL, Array)
wordpress-wordpress-1  | #12 /var/www/html/wp-includes/plugin.php(517): WP_Hook->do_action(Array)
wordpress-wordpress-1  | #13 /var/www/html/wp-settings.php(695): do_action('init')
wordpress-wordpress-1  | #14 /var/www/html/wp-config.php(133): require_once('/var/www/html/w...')
wordpress-wordpress-1  | #15 /var/www/html/wp-load.php(50): require_once('/var/www/html/w...')
wordpress-wordpress-1  | #16 /var/www/html/wp-admin/admin.php(34): require_once('/var/www/html/w...')
wordpress-wordpress-1  | #17 {main}
wordpress-wordpress-1  |   thrown in /var/www/html/pg4wp/driver_pgsql_rewrite.php on line 26