Textpattern CMS support forum

You are not logged in. Register | Login | Help

#81 2008-11-24 03:01:07

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 8,493
Website

Re: smd_query: Talk to the database directly via SQL

Alright folks, this is the big one. smd_query goes ballistic.

v0.2 | compressed features:

  • <txp:variable /> support so you can read values from those lovely things directly into a query instead of having to embed them tag-in-tag stylie and get hung up on quoting issues
  • URL variable support: yes, I took the plunge, you can now take arbitrary user input off the address bar and plug it into a query as well, making all manner of crazy HTML form stuff possible. But PLEASE PLEASE PLEASE read the docs (especially the ‘Filtering and injection’ part) on how to help keep your database safe when using this feature. See this: *scrub scrub scrub* ? That’s me washing my hands of any wrongdoing this plugin may cause ;-)
  • To complement the URL variable ability are a slew of security features. By default all URL variables are run internally through gps() and doSlash() to make them as safe as possible. I’ve not managed to get an injection attack past it all evening (doesn’t mean it’s not possible of course). For all the security muffins out there, you can further filter the input by specifying characters that you will allow or disallow in user variables. The urlfilter and urlreplace attributes are advanced features (they’re regexes) but are well worth befriending if you are going to allow user input in your queries. If you have any hassles with them, there are a bajillion resources on the web or, failing that, shout here and someone who eats regular expressions for breakfast will help
  • You can specify default values in the event a variable fails a check or is missing, e.g. if you restrict a variable to the words ford, jaguar and toyota and someone supplies “nissan”, that variable ‘fails’ and returns nothing. If you have set up a default value for that variable then it will come into play at that point. This allows your queries to degrade more gracefully in the event of bogus input
  • Some convenience attributes delim and paramdelim have been added in case you’re working with complex filters that require those characters

As ever, use this tag as a last resort when all built-in tags and other plugins fail you, or when you just need to write some seriously powerful apps without getting your fingers dirty in PHP. Post here if you come up with nifty queries that might help others, or you do something mad like rewrite Second Life in TXP! The floor is also always open if you find a way to improve the plugin, need a hand getting it working or (gasp!) find a bug.

I know I always seem to say this when I release or update a plugin but it’s phenomenal what you can do with this sucker. Nobody has any excuse for not being able to do anything ever again :-D

Play safe.

Last edited by Bloke (2008-11-24 03:15:10)


The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.

Txp Builders – finely-crafted code, design and Txp

Offline

#82 2008-11-24 16:59:40

mrdale
Moderator
From: Walla Walla
Registered: 2004-11-19
Posts: 2,201
Website

Re: smd_query: Talk to the database directly via SQL

Huzzah! er… my brain hurts… Horray!

Offline

#83 2009-02-21 21:20:41

gomedia
Plugin Author
Registered: 2008-06-01
Posts: 1,224
Website

Re: smd_query: Talk to the database directly via SQL

Hi Stef, just been playing with your top smd_query plugin. Should be very useful for getting boy scouts out of horses hooves.

I’m a little confused though. In your Example 8 from the plugin help:

<txp:smd_query query="SELECT Title
     FROM textpattern
     WHERE status = '?user_status'"
     urlfilter="/[^1-5]/"
     defaults="user_status: 4">
   <txp:permlink>{Title}</txp:permlink>
</txp:smd_query>

Shouldn’t <txp:permlink> be <txp:permlink id={id}">? Because without the id="{id}" bit I get lots of:

Tag error: <txp:permlink> -> Textpattern Notice: Article tags cannot be used outside an article context on line 2075

Cheers,

Adi

Offline

#84 2009-02-21 22:38:24

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 8,493
Website

Re: smd_query: Talk to the database directly via SQL

gomedia wrote:

Example 8 from the plugin help… Shouldn’t <txp:permlink> be <txp:permlink id={id}">?

For purism, yes. Good catch. Although if you are trying to display articles from a query then it kind of loosely implies that the smd_query should be wrapped in <txp:if_individual_article /> tags or TXP will complain, as you found.

That’s the downside to giving raw SQL access in a plugin: you are still bound by TXP’s law (or is that lore?!) and have to work within its parameters in order to achieve sensible (read: error free) output.

But I should probably either wrap the example in the conditional or add the id="{id}" for completeness, so thanks for the report. Since there are no new features on the horizon I’ll fix the docs so any new downloads get the alterations.


The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.

Txp Builders – finely-crafted code, design and Txp

Offline

#85 2009-02-21 22:49:27

gomedia
Plugin Author
Registered: 2008-06-01
Posts: 1,224
Website

Re: smd_query: Talk to the database directly via SQL

Thanks Stef. This was one of the first examples I tried & it threw me a bit. Perhaps next time I’ll start with Example 1 like everybody else!

Offline

#86 2009-04-09 17:13:19

eivl
Member
Registered: 2009-04-09
Posts: 20

Re: smd_query: Talk to the database directly via SQL

Hello, first time user of Textpattern and i have found many of your plugins usefull.

I am getting info from a php calendar called supercali event calendar, i have the calendar database in the same database as Txp, with your plugin i have a sql query (tested in phpmyadmin) that gets the name of an event and that from todays date-time and later.

SELECT DISTINCT events.event_id, events.title,events.description, dates.date, dates.event_id,dates.end_date from events,dates WHERE events.event_id=dates.event_id and dates.end_date > now() order by dates.end_date asc

i have more info then i need in the end, but the part that is not working is … dates.end_date > now() …

if anyone can help me with this or maby have a better idea. the output should only be current events, not including old ones (where end_date is lower then systemdate)

Offline

#87 2009-04-09 17:25:05

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 8,493
Website

Re: smd_query: Talk to the database directly via SQL

eivl wrote:

but the part that is not working is … dates.end_date > now() …

When you say “not working”, do you mean it’s giving errors, or not returning what you expect? A few things that might help track this down:

  1. Put site in debugging mode to check there are no actual errors the plugin gives back
  2. Use debug="2" in the smd_query tag to see if the plugin’s debug gives you any decent diagnostic info
  3. Confirm you are using TXP >= 4.0.7 (otherwise the > sign in the query will cause the plugin to fall over)
  4. Post your actual, full smd_query tag
  5. Post a tag trace

Or perhaps try smd_calendar so you can do the date stuff in-house without smd_query :-p

Last edited by Bloke (2009-04-09 17:26:08)


The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.

Txp Builders – finely-crafted code, design and Txp

Offline

#88 2009-04-09 17:29:25

eivl
Member
Registered: 2009-04-09
Posts: 20

Re: smd_query: Talk to the database directly via SQL

Bloke wrote:

eivl wrote:

but the part that is not working is … dates.end_date > now() …

When you say “not working”, do you mean it’s giving errors, or not returning what you expect? A few things that might help track this down:

  1. Put site in debugging mode to check there are no actual errors the plugin gives back
  2. Use debug="2" in the smd_query tag to see if the plugin’s debug gives you any decent diagnostic info
  3. Confirm you are using TXP >= 4.0.7 (otherwise the > sign in the query will cause the plugin to fall over)
  4. Post your actual, full smd_query tag
  5. Post a tag trace

Or perhaps try smd_calendar instead of a 3rd party calendar :-p

Thanks for the quick reply, not working means no output and no errors, i will try to put the site in debugging mode and use debug=2 as well.

i use the latest TXP version.

full tag:

<txp:smd_query query=“SELECT DISTINCT events.event_id, events.title,events.description, dates.date, dates.event_id,dates.end_date from events,dates WHERE events.event_id=dates.event_id and dates.end_date > now() order by dates.end_date asc”> <txp:permlink>{title} {description} {date} {end_date} </txp:permlink>
</txp:smd_query>

the reason for using 3rd party celendar is because i am not the user of the site, the person who wants it is familiar with putting up events in supercali =)

———

EDIT:

in debugging mode and with debug=“2” the site doesnt give me anything

Last edited by eivl (2009-04-09 17:35:31)

Offline

#89 2009-04-09 17:42:38

eivl
Member
Registered: 2009-04-09
Posts: 20

Re: smd_query: Talk to the database directly via SQL

Tag Trace with the problem (lower is the same trace, but then i have removed the now() function)

<!— Runtime: 0.0331 —>
<!— Query time: 0.009439 —>
<!— Queries: 18 —>
<!— Memory: 2456Kb, <txp:link_to_next> —>
<!— txp tag trace:
[SQL (0.00057315826416016): select name, data from txp_lang where lang=‘en-us’ AND ( event=‘public’ OR event=‘common’)]
[SQL (0.0010290145874023): select name, code, version from txp_plugin where status = 1 AND type IN (0,1) order by load_order]
[SQL (0.00019693374633789): select name,code,version from txp_plugin where status = 1 AND name=‘smd_lib’]
[SQL (0.00053501129150391): select ID,Section from textpattern where ID = 8 and Status >= 4 limit 1]
[SQL (0.00020503997802734): select page, css from txp_section where name = ‘articles’ limit 1]
[SQL (0.00050806999206543): select *, unix_timestamp(Posted) as uPosted, unix_timestamp(Expires) as uExpires, unix_timestamp(LastMod) as uLastMod from textpattern where ID=8 and Status in (4,5)]
[article 8]
[SQL (0.00038599967956543): select ID, Title, url_title, unix_timestamp(Posted) as uposted from textpattern where Posted > ’2009-04-08 19:52:37’ and Section = ‘articles’ and Status=4 and Posted < now() and (now() <= Expires or Expires = ’0000-00-00 00:00:00’) order by Posted asc limit 1]
[SQL (0.001223087310791): select ID, Title, url_title, unix_timestamp(Posted) as uposted from textpattern where Posted < ’2009-04-08 19:52:37’ and Section = ‘articles’ and Status=4 and Posted < now() and (now() <= Expires or Expires = ’0000-00-00 00:00:00’) order by Posted desc limit 1]
[SQL (0.0010409355163574): select host from txp_log where ip=‘87.76.120.65’ limit 1]
[SQL (0.00086688995361328): insert into txp_log set `time`=now(),page=’/V2/index.php?id=8’,ip=‘87.76.120.65’,host=‘87.76.120.65’,refer=’‘,status=‘200’,method=‘GET’]
[SQL (0.0002291202545166): select user_html from txp_page where name=‘archive’]
[Page: archive]
<txp:page_title />
<txp:site_url />
<txp:feed_link flavor=“atom” format=“link” label=“Atom” />
<txp:feed_link flavor=“rss” format=“link” label=“RSS” />
<txp:css format=“link” />
<txp:rsd />
<txp:linklist category=“meny” break=” “ sort=“date” /> [SQL (0.00028419494628906): select *, unix_timestamp(date) as uDate from txp_link where category IN (‘meny’) order by date ] [SQL (0.00018906593322754): select Form from txp_form where name=‘plainlinks’] [Form: plainlinks] <txp:linkdesctitle /> [Form: plainlinks] <txp:linkdesctitle /> [Form: plainlinks] <txp:linkdesctitle /> [Form: plainlinks] <txp:linkdesctitle /> [Form: plainlinks] <txp:linkdesctitle /> [Form: plainlinks] <txp:linkdesctitle />
<txp:if_category> [<txp:if_category>: false] <txp:if_search> [<txp:if_search>: false] </txp:if_search> <txp:article limit=“5” /> [SQL (0.00025701522827148): select Form from txp_form where name=‘default’] [Form: default] <txp:if_individual_article> [<txp:if_individual_article>: true] <txp:permlink> <txp:title /> </txp:permlink> </txp:if_individual_article> <txp:posted /> <txp:body /> <txp:permlink> </txp:permlink> <txp:author /> [SQL (0.00039792060852051): select RealName from txp_users where `name` = ‘eivl’ limit 1] <txp:category1 title=“1” link=“1” /> <txp:category2 title=“1” link=“1” /> <txp:comments_invite wraptag=“p” /> <txp:site_url /> [SQL (0.00026202201843262): select Form from txp_form where name=‘comments_display’] [Form: comments_display] <txp:text item=“comment” /> <txp:comments_invite textonly=“1” showalways=“1” showcount=“0” /> <txp:comments /> <txp:if_comments_preview> [<txp:if_comments_preview>: false] </txp:if_comments_preview> <txp:if_comments_allowed> [<txp:if_comments_allowed>: false] <txp:text item=“comments_closed” /> </txp:if_comments_allowed>
</txp:if_category>
<txp:if_individual_article> [<txp:if_individual_article>: true] <txp:site_url /> <txp:link_to_prev> [SQL (0.0003669261932373): select ID as thisid, Section as section, Title as title, url_title, unix_timestamp(Posted) as posted from textpattern where ID = 2] <txp:prev_title /> </txp:link_to_prev> <txp:link_to_next> </txp:link_to_next>
</txp:if_individual_article>
[ ~~~ secondpass ~~~ ] —>

Tag trace without the now() function

<!— Runtime: 0.0463 —>
<!— Query time: 0.010669 —>

<!— Queries: 19 —>
<!— Memory: 2465Kb, <txp:link_to_next> —>
<!— txp tag trace:
[SQL (0.00075578689575195): select name, data from txp_lang where lang=‘en-us’ AND ( event=‘public’ OR event=‘common’)]
[SQL (0.0016450881958008): select name, code, version from txp_plugin where status = 1 AND type IN (0,1) order by load_order]
[SQL (0.00024104118347168): select name,code,version from txp_plugin where status = 1 AND name=‘smd_lib’]
[SQL (0.00077486038208008): select ID,Section from textpattern where ID = 8 and Status >= 4 limit 1]
[SQL (0.00018310546875): select page, css from txp_section where name = ‘articles’ limit 1]
[SQL (0.00055098533630371): select *, unix_timestamp(Posted) as uPosted, unix_timestamp(Expires) as uExpires, unix_timestamp(LastMod) as uLastMod from textpattern where ID=8 and Status in (4,5)]
[article 8]
[SQL (0.00052595138549805): select ID, Title, url_title, unix_timestamp(Posted) as uposted from textpattern where Posted > ’2009-04-08 19:52:37’ and Section = ‘articles’ and Status=4 and Posted < now() and (now() <= Expires or Expires = ’0000-00-00 00:00:00’) order by Posted asc limit 1]
[SQL (0.0010180473327637): select ID, Title, url_title, unix_timestamp(Posted) as uposted from textpattern where Posted < ’2009-04-08 19:52:37’ and Section = ‘articles’ and Status=4 and Posted < now() and (now() <= Expires or Expires = ’0000-00-00 00:00:00’) order by Posted desc limit 1]
[SQL (0.00093507766723633): select host from txp_log where ip=‘87.76.120.65’ limit 1]
[SQL (0.00075387954711914): insert into txp_log set `time`=now(),page=’/V2/index.php?id=8’,ip=‘87.76.120.65’,host=‘87.76.120.65’,refer=’‘,status=‘200’,method=‘GET’]
[SQL (0.0001828670501709): select user_html from txp_page where name=‘archive’]
[Page: archive]
<txp:page_title />
<txp:site_url />
<txp:feed_link flavor=“atom” format=“link” label=“Atom” />
<txp:feed_link flavor=“rss” format=“link” label=“RSS” />
<txp:css format=“link” />
<txp:rsd />
<txp:linklist category=“meny” break=” “ sort=“date” /> [SQL (0.00023293495178223): select *, unix_timestamp(date) as uDate from txp_link where category IN (‘meny’) order by date ] [SQL (0.00022482872009277): select Form from txp_form where name=‘plainlinks’] [Form: plainlinks] <txp:linkdesctitle /> [Form: plainlinks] <txp:linkdesctitle /> [Form: plainlinks] <txp:linkdesctitle /> [Form: plainlinks] <txp:linkdesctitle /> [Form: plainlinks] <txp:linkdesctitle /> [Form: plainlinks] <txp:linkdesctitle />
<txp:if_category> [<txp:if_category>: false] <txp:if_search> [<txp:if_search>: false] </txp:if_search> <txp:article limit=“5” /> [SQL (0.00019502639770508): select Form from txp_form where name=‘default’] [Form: default] <txp:if_individual_article> [<txp:if_individual_article>: true] <txp:permlink> <txp:title /> </txp:permlink> </txp:if_individual_article> <txp:posted /> <txp:body /> <txp:smd_query debug=“2” query=“SELECT DISTINCT events.event_id, events.title,events.description, dates.date, dates.event_id,dates.end_date from events,dates WHERE events.event_id=dates.event_id”> [SQL (0.00035595893859863): SELECT DISTINCT events.event_id, events.title,events.description, dates.date, dates.event_id,dates.end_date from events,dates WHERE events.event_id=dates.event_id] [<txp:smd_query debug=“2” query=“SELECT DISTINCT events.event_id, events.title,events.description, dates.date, dates.event_id,dates.end_date from events,dates WHERE events.event_id=dates.event_id”>: true] <txp:permlink> </txp:permlink> <txp:permlink> </txp:permlink> <txp:permlink> </txp:permlink> <txp:permlink> </txp:permlink> <txp:permlink> </txp:permlink> <txp:permlink> </txp:permlink> </txp:smd_query> <txp:author /> [SQL (0.00018095970153809): select RealName from txp_users where `name` = ‘eivl’ limit 1] <txp:category1 title=“1” link=“1” /> <txp:category2 title=“1” link=“1” /> <txp:comments_invite wraptag=“p” /> <txp:site_url /> [SQL (0.00017094612121582): select Form from txp_form where name=‘comments_display’] [Form: comments_display] <txp:text item=“comment” /> <txp:comments_invite textonly=“1” showalways=“1” showcount=“0” /> <txp:comments /> <txp:if_comments_preview> [<txp:if_comments_preview>: false] </txp:if_comments_preview> <txp:if_comments_allowed> [<txp:if_comments_allowed>: false] <txp:text item=“comments_closed” /> </txp:if_comments_allowed>
</txp:if_category>
<txp:if_individual_article> [<txp:if_individual_article>: true] <txp:site_url /> <txp:link_to_prev> [SQL (0.00057697296142578): select ID as thisid, Section as section, Title as title, url_title, unix_timestamp(Posted) as posted from textpattern where ID = 2] <txp:prev_title /> </txp:link_to_prev> <txp:link_to_next> </txp:link_to_next>
</txp:if_individual_article>
[ ~~~ secondpass ~~~ ] —>

Last edited by eivl (2009-04-09 17:43:19)

Offline

#90 2009-04-09 17:45:42

eivl
Member
Registered: 2009-04-09
Posts: 20

Re: smd_query: Talk to the database directly via SQL

It looks like the query isnt able to run when including now() with the < or > operator, might even be true for all WHERE instances. using SQL alias works, used just now for debugging.

Offline

Board footer

Powered by FluxBB