SQL commands in post content cause failure to save and trigger ConfigServer Security & Firewall (solved by sending some API requests as a JSON blob instead of form encoded data to try to avoid triggering some modsec rules, see 5971)

[ZebulanStanphill]

Issue Overview

Starting with Gutenberg 2.3 and still in 2.5, I have had an issue where I was unable to save / publish posts containing phrases that correspond to certain SQL commands, including:

• "select" + anything (including multiple blocks) or nothing + "from"
• "create database"
• "alter database"
• "create table"
• "alter table"
• "drop table"

(Note that these phrases have to be in lowercase to trigger the error.)

Trying to save a post containing any of these phrases will fail, giving the "Updating failed" notification. I'm using the latest version of WordPress (4.9.4) and I have tested this without any plugins installed and with the Twenty Seventeen theme, I've tested it with posts, pages, and custom post types, and I've tested it on both Firefox Nightly and Chromium. The error happens on all of them. This error does not occur with Gutenberg 2.2, and it does not occur with the Classic Editor.

When I check the JavaScript console on Chromium, I see this error:

load-scripts.php?c=1&load[]=jquery-core,jquery-migrate,utils,jquery-ui-core,jquery-ui-widget,jquery-ui-tabs,jquery-form,underscore,jquery-ui-mouse,backbone,&load[]=shortcode,moxiejs,plupload,jquery-ui-sortable&ver=4.9.4:4 POST https://supergeniuszeb.com/wp-json/wp/v2/posts/9553 500 (Internal Server Error)
send @ load-scripts.php?c=1&load[]=jquery-core,jquery-migrate,utils,jquery-ui-core,jquery-ui-widget,jquery-ui-tabs,jquery-form,underscore,jquery-ui-mouse,backbone,&load[]=shortcode,moxiejs,plupload,jquery-ui-sortable&ver=4.9.4:4
ajax @ load-scripts.php?c=1&load[]=jquery-core,jquery-migrate,utils,jquery-ui-core,jquery-ui-widget,jquery-ui-tabs,jquery-form,underscore,jquery-ui-mouse,backbone,&load[]=shortcode,moxiejs,plupload,jquery-ui-sortable&ver=4.9.4:4
b @ api-request.min.js?ver=4.9.4:1
wp.apiRequest @ post.php?post=9553&action=edit:234
REQUEST_POST_UPDATE @ index.js?ver=1522268292:11
(anonymous) @ index.js?ver=1522268292:11
(anonymous) @ index.js?ver=1522268292:6
Le @ react-dom.min.3583f8be.js:92
invokeGuardedCallback @ react-dom.min.3583f8be.js:91
invokeGuardedCallbackAndCatchFirstError @ react-dom.min.3583f8be.js:91
cd @ react-dom.min.3583f8be.js:15
Me @ react-dom.min.3583f8be.js:94
af @ react-dom.min.3583f8be.js:94
da @ react-dom.min.3583f8be.js:16
Zb @ react-dom.min.3583f8be.js:17
pb @ react-dom.min.3583f8be.js:123
yf @ react-dom.min.3583f8be.js:34
batchedUpdates @ react-dom.min.3583f8be.js:169
cc @ react-dom.min.3583f8be.js:26
jc @ react-dom.min.3583f8be.js:35

This does not happen for everyone, but it happens consistently for me. I know it has something to do with the firewall on my server detecting the SQL phrases in the post content. My website is using HTTPS. My server host is Liquid Web. It has ConfigServer Security & Firewall enabled. Originally, after multiple subsequent tries to save a post containing one of the SQL phrases, my IP would be banned from the server. I had to whitelist it in the csf.allow file of the firewall in order to keep testing and narrow down the issue. I checked some of the logs, and here is what I found after trying to save a post containing the phrase "alter table":

In /usr/local/apache/logs/error_log:

[Wed Mar 28 23:16:21.467466 2018] [:error] [pid 77870:tid 140034229323520] [client 47.186.212.157] ModSecurity: Access denied with code 500 (phase 2). Pattern match "((alter|create|drop)[[:space:]]+(column|database|procedure|table)|delete[[:space:]]+from|update.+set.+=)" at ARGS:content. [file "/usr/local/apache/conf/modsec2.user.conf"] [line "254"] [id "300015"] [rev "1"] [msg "Generic SQL injection protection"] [severity "CRITICAL"] [hostname "supergeniuszeb.com"] [uri "/wp-json/wp/v2/posts/9553"] [unique_id "WrxahUPhvRcAATAuayYAAAKX"]

In /usr/local/apache/logs/modsec_audit.log:

--b592ee5b-A--
[28/Mar/2018:23:16:22 --0400] WrxahUPhvRcAATAuayYAAAKX 47.186.212.157 48116 67.225.189.23 443
--b592ee5b-B--
POST /wp-json/wp/v2/posts/9553 HTTP/1.1
Host: supergeniuszeb.com
Connection: keep-alive
Content-Length: 571
Origin: https://supergeniuszeb.com
User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/65.0.3325.181 Safari/537.36
Content-Type: application/x-www-form-urlencoded; charset=UTF-8
Accept: /
X-Requested-With: XMLHttpRequest
X-WP-Nonce: 23246b7cca
X-HTTP-Method-Override: PUT
DNT: 1
Referer: https://supergeniuszeb.com/wp-admin/post.php?post=9553&action=edit
Accept-Encoding: gzip, deflate, br
Accept-Language: en-US,en;q=0.9
Cookie: wordpress_test_cookie=WP+Cookie+check; wordpress_logged_in_e250e674e6402ebc0c96bdabaf5a4013=admin%7C1522435461%7C0INoK4M6dyryw3b07NYo27KF8gjdHbSrzbMxPtJg1eg%7C84015bad7bb080918069cf43234035f9a44dffa6dc6fa40f8b7a4bbae933484d; wp-settings-1=libraryContent%3Dbrowse%26editor%3Dtinymce%26advImgDetails%3Dhide%26hidetb%3D0%26post_dfw%3Doff%26imgsize%3Dfull%26mfold%3Do%26urlbutton%3Dnone%26posts_list_mode%3Dlist; wp-settings-time-1=1522262661; et-editor-available-post-9553-bb=bb
--b592ee5b-C--
content=%3C!--+wp%3Aparagraph+--%3E%0A%3Cp%3Ealter+tablx%3C%2Fp%3E%0A%3C!--+%2Fwp%3Aparagraph+--%3E%0A%0A%3C!--+wp%3Aimage+%7B%22id%22%3A2048%7D+--%3E%0A%3Cfigure+class%3D%22wp-block-image%22%3E%3Cimg+src%3D%22https%3A%2F%2Fsupergeniuszeb.com%2Fwp-content%2Fuploads%2F2018%2F01%2Fscreenshot-from-SGZ-Plays-MC-E3.png%22+alt%3D%22%22+class%3D%22wp-image-2048%22+%2F%3E%0A++++%3Cfigcaption%3Eselect+frox%3C%2Ffigcaption%3E%0A%3C%2Ffigure%3E%0A%3C!--+%2Fwp%3Aimage+--%3E%0A%0A%3C!--+wp%3Aparagraph+--%3E%0A%3Cp%3Ealter+table%3C%2Fp%3E%0A%3C!--+%2Fwp%3Aparagraph+--%3E&id=9553
--b592ee5b-F--
HTTP/1.1 500 Internal Server Error
X-Powered-By: PHP/5.6.30
X-Robots-Tag: noindex
Link: https://supergeniuszeb.com/wp-json/; rel="https://api.w.org/"
X-Content-Type-Options: nosniff
Access-Control-Expose-Headers: X-WP-Total, X-WP-TotalPages
Access-Control-Allow-Headers: Authorization, Content-Type
Expires: Wed, 11 Jan 1984 05:00:00 GMT
Cache-Control: no-cache, must-revalidate, max-age=0
X-WP-Nonce: 23246b7cca
Allow: GET, POST, PUT, PATCH, DELETE
Access-Control-Allow-Origin: https://supergeniuszeb.com
Access-Control-Allow-Methods: OPTIONS, GET, POST, PUT, PATCH, DELETE
Access-Control-Allow-Credentials: true
Vary: Origin
Connection: close
Content-Type: application/json; charset=UTF-8
--b592ee5b-H--
Message: Access denied with code 500 (phase 2). Pattern match "((alter|create|drop)[[:space:]]+(column|database|procedure|table)|delete[[:space:]]+from|update.+set.+=)" at ARGS:content. [file "/usr/local/apache/conf/modsec2.user.conf"] [line "254"] [id "300015"] [rev "1"] [msg "Generic SQL injection protection"] [severity "CRITICAL"]
Action: Intercepted (phase 2)
Apache-Handler: fcgid-script
Stopwatch: 1522293381465620 548360 (- - -)
Stopwatch2: 1522293381465620 548360; combined=586, p1=97, p2=486, p3=0, p4=0, p5=3, sr=41, sw=0, l=0, gc=0
Producer: ModSecurity for Apache/2.9.0 (http://www.modsecurity.org/).
Server: Apache
Engine-Mode: "ENABLED"

Steps to Reproduce

1. Create a new post.
2. Put any of the previously listed phrases into the post content. It can be in paragraphs, lists, image captions... it doesn't matter.
3. Try saving the draft or publishing the post.
4. The post will not save and you will get the "Updating failed" notification.

Expected Behavior

The post should save properly and shouldn't give any HTTP status code 500 errors.

Current Behavior

Trying to save a post containing the previously listed SQL commands will fail, and the "Updating failed" notification will appear. An HTTP status code 500 error will occur.

Possible Solution

It definitely has something to do with the server firewall detecting SQL phrases in the post content and thinking that some kind of SQL injection attack is trying to be performed. Also, in one of the previously linked logs, I noticed Expires: Wed, 11 Jan 1984 05:00:00 GMT... I would guess that expiration date is unintentional, since 1984 was a considerably long time ago; I am not sure if that has any relevance or not, but I thought I would point it out.

Related Issues and/or PRs

#5675

Pinging @pento, as requested.

[pento]

Thank you for the detailed report, @SuperGeniusZeb!

As is mentioned in your logs, rule 300015 is a "generic SQL injection protection" rule, which is known to cause problems for WordPress (or any CMS, for that matter). The usual way to get modsec working with WordPress is to whitelist the handful of URLs that could trigger this (or similar) rules. Could you have a look in your modsec config files (probably found somewhere under /usr/local/apache/conf/) from something that resembles this?

<LocationMatch "/wp-admin/post.php">
SecRuleRemoveById 300013 300014 300015 300016 300017
</LocationMatch>

There will likely be similar rules for other locations, I'm particularly interested in anything that applies to /wp-json URLs. As the requests worked in Gutenberg 2.3, but stopped in 2.4, that suggests there's a rule that whitelists based on some difference between the two: if we can figure that out, we can potentially tweak our requests to avoid triggering these modsec rules.

[ZebulanStanphill]

@pento Actually, the requests worked in Gutenberg 2.2, but stopped working in 2.3 and onward. I have reworded that sentence at the start of the issue to be more clear. Tomorrow I will try and see what I can find in the modsec config files and post it here.

[ZebulanStanphill]

@pento In the modsec rules file ( labelled "Modsec2 rules v0.7-5" in a comment at the top... I don't know what the specific file name is since I don't know how to get a view the directory structure of the server or if I even have access), I found these sections that reference WordPress and/or SQL:
https://pastebin.com/qGzWnvx4

Also, I took a quick look through the commits made after the release of Gutenberg 2.2 (the last version that does not have this issue), and I noticed these commits, which I think are the ones most likely to be related to the issue:

e3da1a1
#5146 "Compat: Remove unnecessary api-request shim (#5146)"

25de703
#4777 "Add an API to add a plugin sidebar (new) (#4777)"

86dbc73
#4226 " Make sure the oembed proxy route applies the WP Embed security mechanism."

23fed49
#5288 "Fix markdown conflicts"

c7a7583
#4877 "Compat: Shim fix for apiRequest with plain permalink (#4877)"

5777c0f
#5288 "Combine if statements, switch to use only post_content"

699d427
#5253 "Framework: Use a single way to call APIs: wp.apiRequest (#5253)"