WHERE contains SQL that is not allowed

Resolved bobbrown99
(@bobbrown99)

3 months, 3 weeks ago
Hi there,

I am trying to get pods to only display if the date is today or in the future. When I try to include the relevant code in a shortcode on the page, I get the error: “Pods Embed Error: WHERE contains SQL that is not allowed.”

The shortcode is:

[pods name="event" template="events" orderby="date_and_time.meta_value ASC" where="CAST( date_and_time.meta_value AS DATETIME ) >= NOW()" ]

It works perfectly fine without the where= SQL component.

I have reviewed other posts here and elsewhere but the solution proposed does not resolve my issue.

To clarify, I have tried:
1. Allowing “unrestricted” and ” restricted” under “Allow SQL clauses to be used in Dynamic Features” in Pods Admin > Settings > Security section
2. Allowing “unrestricted” in the specific pod option
3. Clearing the Pods cache
4. Turned off all caches for the website
5. Clearing browser cache
6. Trying multiple browsers
The pod is public and dynamic features are enabled.

The page I need help with: [log in to see the link]

Viewing 7 replies - 1 through 7 (of 7 total)

Plugin Support pdclark
(@pdclark)

2 months, 3 weeks ago

Typecasting NOW() should cause the comparison to work.

where="CAST( date_and_time.meta_value AS DATETIME ) >= CAST( NOW() AS DATETIME )"

Thread Starter bobbrown99
(@bobbrown99)

2 months, 3 weeks ago

Hi there,

Thanks for your response. I have just tried what you suggested and I get the same error: “Pods Embed Error: WHERE contains SQL that is not allowed.”

For clarity the full shortcode is:

[pods name="event" template="events" orderby="date_and_time.meta_value ASC" where="CAST( date_and_time.meta_value AS DATETIME ) >= CAST( NOW() AS DATETIME )"]

Plugin Support pdclark
(@pdclark)

2 months, 1 week ago

On Pods Admin > Settings, change Allow SQL clauses to be used in Dynamic Features to Restricted or Unrestricted, not Disable

Thread Starter bobbrown99
(@bobbrown99)

2 months ago

Hi there,

As per my original message, I have already done that. Please see this screenshot below for my current setting:

View post on imgur.com
Plugin Support pdclark
(@pdclark)

2 months ago
Testing again, it appears there is a recent change causing > to be flagged as a false positive for security reasons.

That issue has been submitted to the GitHub team for resolution. In the meantime, this worked in testing and has the same effect:
```
where="CAST( date_and_time.meta_value AS DATETIME ) BETWEEN NOW() - INTERVAL 5 HOUR AND '9999-12-31 23:59:59'"
```
…where NOW() - 5 HOUR is any desired timezone offset, as the calculation of current time will be based on the database timezone configuration separate from WP.
Thread Starter bobbrown99
(@bobbrown99)

2 months ago

Thank you, that has fixed it

Plugin Author Scott Kingsley Clark
(@sc0ttkclark)

1 month, 1 week ago

Just closing the loop here, Paul created a bug report at https://github.com/pods-framework/pods/issues/7482 and I’ve come up with a solution that will help in the future.

First, if the malformed shortcode is detected by Pods then it will provide this helpful message:

Malformed shortcode detected. WordPress shortcodes have limited support for certain characters in attributes. If you are using the characters “<” or “>” in an attribute then you must switch them to “__LESS_THAN__” or “__GREATER_THAN__” respectively. Other options include “__LESS_THAN_OR_EQUAL__” and “__GREATER_THAN_OR_EQUAL__“.

Second, there’s no way for Pods to attempt to automatically fix the shortcode issue because some content is completely lost due to the WP shortcode parsing issue with the < and > characters. Specifically in some circumstances the actual < or > character that caused it in the first place.

I believe that the helpful text warning will alleviate the problem in the future for others going down this path and it will point them to a better solution for themselves.

This will be released in Pods 3.3.5

Viewing 7 replies - 1 through 7 (of 7 total)

You must be logged in to reply to this topic.