Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SQL_Translations::on_update_to_merge() can be confused by SQL inside text fields #327

Open
rmc47 opened this Issue Feb 27, 2019 · 0 comments

Comments

Projects
None yet
1 participant
@rmc47
Copy link
Contributor

rmc47 commented Feb 27, 2019

When SQL_Translations::on_update_to_merge() is called, we've already re-inserted the quoted string values that had been stripped out before other translations had taken place.

This means that the regexps in on_update_to_merge() can be confused by SQL-like syntax inside these string values. For example:

    $wpdb->query("INSERT INTO [wp_posts] ([post_author], [post_date], [post_date_gmt], [post_content], [post_content_filtered], [post_title], [post_excerpt], [post_status], [post_type], [comment_status], [ping_status], [post_password], [post_name], [to_ping], [pinged], [post_modified], [post_modified_gmt], [post_parent], [menu_order], [post_mime_type], [guid]) VALUES (1, '2019-02-27 23:23:12', '0000-00-00 00:00:00', 'Test content', '', 'Test', 'INSERT ... ON DUPLICATE KEY UPDATE', 'draft', 'ssc_blog_post', 'closed', 'closed', '', '', '', '', '2019-02-27 23:23:12', '0001-01-01 00:00:00', 0, 0, '', '')");

This will cause a SQL error on its first pass as a completely unrelated result of the invalid date, resulting in it hitting the translation routine.

Having done so though, the result of translation is quite spectacular 😄:

2019-02-27 23:37:19 Error Code: 241 -- Begin Query translation attempt:
INSERT INTO [wp_posts] ([post_author], [post_date], [post_date_gmt], [post_content], [post_content_filtered], [post_title], [post_excerpt], [post_status], [post_type], [comment_status], [ping_status], [post_password], [post_name], [to_ping], [pinged], [post_modified], [post_modified_gmt], [post_parent], [menu_order], [post_mime_type], [guid]) VALUES (1, '2019-02-27 23:23:12', '0000-00-00 00:00:00', 'Test content', '', 'Test', 'INSERT ... ON DUPLICATE KEY UPDATE', 'draft', 'ssc_blog_post', 'closed', 'closed', '', '', '', '', '2019-02-27 23:23:12', '0001-01-01 00:00:00', 0, 0, '', '')
2019-02-27 23:37:19 -- Translation result:
MERGE INTO  [wp_posts]  WITH (HOLDLOCK) AS target USING (SELECT 1 as [post_author],'2019-02-27 23:23:12' as [post_date],'0001-01-01 00:00:00' as [post_date_gmt],'Test content' as [post_content],'' as [post_content_filtered],'Test' as [post_title],'INSERT ... ON DUPLICATE KEY UPDATE' as [post_excerpt],'draft' as [post_status],'ssc_blog_post' as [post_type],'closed' as [comment_status],'closed' as [ping_status],'' as [post_password],'' as [post_name],'' as [to_ping],'' as [pinged],'2019-02-27 23:23:12' as [post_modified],'0001-01-01 00:00:00' as [post_modified_gmt],0 as [post_parent],0 as [menu_order],'' as [post_mime_type],'' as [guid]) AS source ([post_author], [post_date], [post_date_gmt], [post_content], [post_content_filtered], [post_title], [post_excerpt], [post_status], [post_type], [comment_status], [ping_status], [post_password], [post_name], [to_ping], [pinged], [post_modified], [post_modified_gmt], [post_parent], [menu_order], [post_mime_type], [guid]) ON (source.[post_author]=target.[post_author] AND source.[post_date]=target.[post_date] AND source.[post_date_gmt]=target.[post_date_gmt] AND source.[post_content]=target.[post_content] AND source.[post_content_filtered]=target.[post_content_filtered] AND source.[post_title]=target.[post_title] AND source.[post_excerpt]=target.[post_excerpt] AND source.[post_status]=target.[post_status] AND source.[post_type]=target.[post_type] AND source.[comment_status]=target.[comment_status] AND source.[ping_status]=target.[ping_status] AND source.[post_password]=target.[post_password] AND source.[post_name]=target.[post_name] AND source.[to_ping]=target.[to_ping] AND source.[pinged]=target.[pinged] AND source.[post_modified]=target.[post_modified] AND source.[post_modified_gmt]=target.[post_modified_gmt] AND source.[post_parent]=target.[post_parent] AND source.[menu_order]=target.[menu_order] AND source.[post_mime_type]=target.[post_mime_type] AND source.[guid]=target.[guid]) WHEN NOT MATCHED THEN INSERT ([post_author], [post_date], [post_date_gmt], [post_content], [post_content_filtered], [post_title], [post_excerpt], [post_status], [post_type], [comment_status], [ping_status], [post_password], [post_name], [to_ping], [pinged], [post_modified], [post_modified_gmt], [post_parent], [menu_order], [post_mime_type], [guid]) VALUES(1, '2019-02-27 23:23:12', '0001-01-01 00:00:00', 'Test content', '', 'Test', 'INSERT ... ON DUPLICATE KEY UPDATE', 'draft', 'ssc_blog_post', 'closed', 'closed', '', '', '', '', '2019-02-27 23:23:12', '0001-01-01 00:00:00', 0, 0, '', '');

Is there a reason why on_update_to_merge translation needs to be called after the preg_data is re-inserted into the query, or could it just be moved up a little earlier? (Doing so seems to do the right thing, but I'm not sure if the current position was deliberate.)

rmc47 added a commit to rmc47/projectnami that referenced this issue Feb 27, 2019

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.