smd_query: Talk to the database directly via SQL

jpdupont · 2008-06-18 19:31:39

Hi,

I want to put some php code in the form.

My simplified form:

<txp:php>
echo "<h2>{Title}</h2>";
$test="{Body_html}";
</txp:php>

My query :

<txp:smd_query query="SELECT * FROM `mydb`.`txp_textpattern` WHERE `txp_textpattern`.`Section` != 'accueil' ORDER BY Posted DESC" form="affiche_article_news" />

When we have some html tags in {Body_html}, we get this error :

Parse error: syntax error, unexpected T_STRING in /home/www/ee1d834bc4a24e2803fc7f7db26ab389/web/textpattern/publish/taghandlers.php(2804) : eval()'d code on line ...

No problem, when I do $test=”{Body}”; or when Body_html have no html tag.

Any idea?

Bloke · 2008-06-18 19:52:04

jpdupont wrote:

When we have some html tags in {Body_html}, we get this error [snip] No problem, when I do $test=”{Body}”; or when Body_html have no html tag.

It’s probably because I call html_entity_decode() which makes the parser cry, or it’ll be some random encoding issue like that.

Would you do me a favour please and add debug="1" or debug="2" (for more verbose output) to your smd_query. If you get any useful info out, please post some of it here (or email me if it’s a lot) and I’ll see if I can work out what I’m not doing that I should be doing! Thanks.

jpdupont · 2008-06-18 20:03:08

Stef,

I just emailed you the url of the page with problem.

Bloke · 2008-06-19 12:16:12

jpdupont

Sorry for the delay in replying, and thanks for the debug output.

This problem is rather bizarre. I can clearly see the error messages on your page but I can’t quite replicate them here. I can use {Body_html} on my server to output stuff fine in most cases. However, I have managed to completely kill (and core dump) my page under certain circumstances — apologies to my hosting company. When this happens, my error logs give similar errors to those you are seeing, such as PHP Parse error: syntax error, unexpected '}' in blahblah.

So I still think this is to do with some characters in the Body_html field that aren’t escaped when I get the result set. Common candidates are the usual double quotes, single quotes, back-ticks, or square and curly brackets.

This all comes down to the reason I am hesitant to “open up” this plugin to use the address bar. At the moment I am performing a simple html_entity_decode() on any query/parameters you supply to the plugin. I should probably be performing a doSlash() as well somewhere, but it always kills the queries with syntax errors. Perhaps this is the wrong approach, but the input side is not what is causing your problem here.

On the output side I made the false assumption that safe_query() or safe_rows() would encode the results but it seems that is down to me as well (which makes sense). Perhaps if I was to html encode every {field} you use before displaying it, those errors would go away. But then you may well see the HTML entity names in your output; so things like <p> might turn into <p> which is not what you want. I’ll have to play with it, but there are so many encoding/decoding functions in PHP or TXP’s core it’s hard to know which to use to avoid this sort of problem! Especially when foreign/Unicode characters are thrown into the mix which I cannot test properly here.

I tried to work out which articles were giving you the problem so I could see if a particular character was the cause, but have so far not found anything the plugin would consider “difficult” to parse. I thought it may have been the quotes in your <txp:image > tags in the body text, but I tried some in mine and it displayed both {Body} and {Body_html} perfectly.

So at the moment I’m out of ideas, sorry. I can only repeat my plea to anyone with knowledge of TXP/PHP/MySQL who can advise me which functions I should be calling to sanitize both the user input and database field output. I will carry on testing and try to work out what’s best but any hints would be greatly appreciated.

A word of warning to everyone using this plugin. DO NOT UNDER ANY CIRCUMSTANCES use anything like the following tag in an article:

<txp:smd_query query="SELECT * FROM textpattern WHERE Body LIKE '%kill_my_server%'">
 {Body_html}
</txp:smd_query>

To do so will very quickly send your server into an infinite loop because Body_html is parsed by TXP. At best you’ll get a 500 Internal Server Error; alternatively you may receive angry e-mails from the administrator or other users of your shared environment demanding your head on a stick.

Last edited by Bloke (2008-06-19 12:19:07)

wet · 2008-06-19 12:27:18

Rule Of Thumb I: Apply doSlash() on everything you feed into the query, use html_specialchar() for everything you retrieve for on-browser display.
Rule Of Thumb II: One size never fits all.

Bloke · 2008-06-19 12:47:39

Thanks wet. I’ll try those two tips out, though from my cursory testing it seems doSlash($query_input) causes:

select * from textpattern where title like '%test%'

to become:

select * from textpattern where title like \'%test%\'

Which gives an error when passed to safe_query(). And when outputting stuff from Body_html, htmlspecialchars() turns:

Ability is a poor man's wealth

Into:

Ability is a poor man&# 8217;s wealth

[space added]

So I’m either doing something else in the code that’s causing the stuff to be doubly encoded, or Rule Of Thumb II has come out to play ;-)

Thank you for giving me some direction. I will continue to test with stuff along these themes; perhaps I need to be more clever and check if the output has already been encoded first… or go on a course to learn how to code properly!

Last edited by Bloke (2008-06-19 12:48:42)

mattia.daldegan · 2008-07-02 14:18:13

Hello to everybody!

I’m pretty new to Textpattern but I’ve been in confidence quite early.

I’ve a BIG problem to solve… :P I post here cause I think smd_query would be the right plugin to solve it.
I need to show a list of all the NOT YET EXPIRED articles of the section “news”. To accomplish this I use custom_1 field to set an expiration date for each article.
I actually do the job through smd_query, this way:

<txp:smd_query query="
   SELECT ID, Title as t, Image as i, Excerpt_html as e
   FROM textpattern
   WHERE (custom_1 &gt;= CURDATE() OR custom_1 = '') AND Section='news'
   ORDER BY Posted DESC;
">
   <div>
      <h2><txp:permlink id="{ID}">{t}</txp:permlink></h2>
      <txp:image id="{i}" />
      <p>{e}</p>
   </div>
</txp:smd_query>

Yes, the BIG problem has not come yet.
This list must be paginated, showing only 5 articles per page.

The question is… “how can I limit the query resultset on the basis of the page selected by the user (that is the value of the GET pg parameter in the URL)?”
Or in other words: “there exists a way to build the query-string dinamically?”

I hope the BIG problem is clear…
Is this the right way to do the job? Or is smd_query the wrong plugin? Can anybody help me?

Thanks in advance, sorry for my bad english. Mattia

PS. Thank you Stef for your great work.

Bloke · 2008-07-02 14:31:37

mattia.daldegan wrote:

“how can I limit the query resultset on the basis of the page selected by the user (that is the value of the GET pg parameter in the URL)?”

Hi mattia. The short answer is no it can’t be done directly. For now.

Long(er) term, the answer is yes. The problem is one of security. I can (and probably will) “open up” the plugin to allow you to read values from the $_GET array but right now I’m not confident it won’t leave a gaping security hole in your site. So I’ve explicitly forbidden use of the GET and POST arrays in queries.

You can probably get round it with some cunning use of the new (in SVN) <txp:variable /> tag, i.e.

<txp:variable name="page" value='<txp:php>echo $_GET['pg'];</txp:php>' />
<txp:smd_query query='your quwery plus a call to <txp:variable name="page" />' />

Failing that, you could perhaps store a sort of SQL stub with placeholders for variables using smd_vars as Sheru tried but I’m not even sure that’s working reliably right now (smd_vars is still experimental and is not fully functional yet).

This is definitely on the todo list, but I’m a bit snowed under right now. Sorry.

Last edited by Bloke (2008-07-02 14:32:14)

jakob · 2008-07-06 17:48:47

I had a play around with smd_query and a current pre-4.07 svn installation today to filter a list of articles according to two categories at once and it’s brilliant. Previously I had achieved this with chh_article_custom but it refused to work with the pre-4.07 version.

I have two select drop-downs that append &c=... or &c1=...&c2=... to the url. I used your smd_if to test for the existence of the GET var and formulated an SQL query string based on the get vars available:

<txp:variable name="sqlmatch" value="" />

<txp:smd_if field="urlvar:c" operator="isused">
<txp:variable name="sqlmatch"
              value=' AND (Category1 = ''<txp:php>echo $_GET["c"];</txp:php>'' OR Category2 = ''<txp:php>echo $_GET["c"];</txp:php>'')' />
</txp:smd_if>

<txp:smd_if field="urlvar:c1,urlvar:c2" operator="isused,isused">
<txp:variable name="sqlmatch"
              value=' AND (Category1 = ''<txp:php>echo $_GET["c1"];</txp:php>'' OR Category2 = ''<txp:php>echo $_GET["c1"];</txp:php>'') 
                      AND (Category1 = ''<txp:php>echo $_GET["c2"];</txp:php>'' OR Category2 = ''<txp:php>echo $_GET["c2"];</txp:php>'')' />
</txp:smd_if>

and then further on…

<txp:smd_query 
query="SELECT * 
       FROM textpattern 
       WHERE Status >= '4' 
       AND Posted < now() 
       AND ( (Section = '?s') )  
       <txp:variable name="sqlmatch" />
       ORDER BY custom_2 desc"
form="test_entry" />

with form: test_entry as follows:

<txp:if_variable name="zebra" value="odd">
  <txp:variable name="zebra" value="even" />
<txp:else />
  <txp:variable name="zebra" value="odd" />
</txp:if_variable>
		  <tr class="<txp:variable name="zebra" />">
		    <td><txp:permlink id="{ID}">{Title}</txp:permlink></td>
		    <td>{custom_3}</td>
		    <td><txp:category name="{Category1}"  title="1" link="1" /></td>
		    <td>{custom_2}</td>
		  </tr>

I put wet’s zebra stripe demo to use and all in all it works very nicely. chh_article_custom is no longer needed. Yay!

A few questions:

Am I laying myself open to SQL INJECTION abuse? And if so how could I limit it (thinking aloud, perhaps check that the value of the GET variable for c/c1/c2 actually exists in the categories db or else reject as not found)?
Is it possible to test for no results (e.g. count=0)? I tried surrounding the smd_query section with chh_if_data but had no luck. According to the debug trace chh:if_data is true even when no entries are found (also when I take out the zebra stripe class stuff).
Textpattern complains that no txp:article appears on the page (which is true as it’s been replaced entirely by smd_query). For the moment I’ve used <txp:article pgonly="1" searchsticky="1" /> further up on the page to silence it, but maybe there’s a better way?
Can one simplify the above use of two categories e.g. is there a more elegant way of appending two (or more) categories to an URl and a respective means of retrieving the (series of) get variables?

Bloke · 2008-07-06 18:24:42

Excellent example, jakob! Great combination of the plugins with <txp:variable />. Thanks for sharing.

jakob wrote:

Am I laying myself open to SQL INJECTION abuse?

Possibly. smd_query doesn’t do any checks or balances on this usage (yet) but your query does look fairly ok.

I’m not sure if this makes any difference in terms of security (probably not) but if I was constructing the query I’d sandwich the txp:variable between a couple of AND clauses simply so that the whole query must “make sense” if someone injects something.

By putting it at the end — as you have done, before the ORDER BY — someone could inject something into the existing query, add a semicolon and then put any random query on the end that would receive the ORDER BY clause. But if you rearrange it like this:

       AND Posted < now() 
       <txp:variable name="sqlmatch" />
       AND ( (Section = '?s') )  
       ORDER BY custom_2 desc"

then anything injected also has to have some extra stuff in it that can be validly prepended to the ‘AND Section’ clause, which is probably (slightly) harder to construct and also limits any 2nd query to the Textpattern table, since that’s the only one in which Section appears (I think). Mind you, I don’t think ‘custom_2’ appears anywhere else either so it probably won’t make much difference here.

As a general security measure though, moving the position of the txp:variable around can sometimes enhance the security slightly, but probably not enough to warrant a “this is rock solid” stamp. By far the best thing is to check the category exists, as you suggested. And if you filter out any ‘dodgy’ characters like semicolons or tags etc in the process, you’re pretty much clear.

Is it possible to test for no results (e.g. count=0)?

Not easily yet, but that’s a good request. You could perhaps surround all the code in your test_entry form with an smd_if to check if {ID} (or some other replacement variable) exists. I will look into adding an if_query conditional tag inside this plugin for convenience.

Textpattern complains that no txp:article appears on the page

Yes it will. That warning is a pain and I’ve never found a way round it, sorry. Perhaps someone else has?

Can one simplify the above use of two categories

At the expense of yet another smd plugin, you could try smd_each. Add the variables to a single URI var (make them comma-delimited or some other char that you can tell smd_each about) then “read” this single variable name and iterate over each thing it contains. You should only then need one smd_if to set the txp:variable. But whether this is any better really depends on your definition of ‘elegant’ :-)

mrdale · 2008-07-06 19:56:58

eek… I’m getting left behind with all this new found power in 4.07 (tags in tags, variables, direct queries) my brain hurts. I need to buy a pitcher of beer for one of you braniacs and just listen for a few hours.

jakob · 2008-07-06 21:35:07

Dale, “brain hurts”: my feelings exactly so I thought I’d try out some of the possibilities.

Great reply, too, Stef! Good idea with shifting the variable, though I can see that it’s still vulnerable. In my actual case I’m using .htaccess to rewrite a human-readable url like /category/this/that to &c1=this&c2=that in the background so that also disguises it a bit more.

And if you filter out any ‘dodgy’ characters like semicolons or tags etc in the process, you’re pretty much clear

Would this actually serve as a general solution? Stripping out all semicolons is easy and semicolons wouldn’t occur in a category name. What other dodgy characters might there be that would need pruning?

By far the best thing is to check the category exists, as you suggested

Yes, that should be rock solid, I guess. Any quick idea on how best to do this? I don’t recall if we already have a ready “if category exists” function somewhere? I’ve been thinking all day and juice is low :-)

But whether this is any better really depends on your definition of ‘elegant’ :-)

I had the feeling that my version was a bit clumsy and that a pro would be able to slice and dice it (or array explode it) ‘just-like-that’. No at least I feel a little better.

Is it possible to test for no results (e.g. count=0)? …

You could perhaps surround all the code in your test_entry form with an smd_if to check if {ID} (or some other replacement variable) exists. I will look into adding an if_query conditional tag inside this plugin for convenience.

I’ve tried all manner of smd_if operator variations with {ID} in the form as well as around the smd_query (with “id”) but without success. I even tried your reversed field=“NULL” value={ID} version. I can get it to respond when there are results but not when there are no hits to the query at all. Is the form processed at all when there are no hits in smd_query? Can one smd_if field="{count}" operator="gt" value="0" or something similar around the smd_query function call? Your example with {count} is built differently with a COUNT(*) as count so I imagine the SQL must be arranged differently.

…

I have one further question but hardly dare ask… but seeing as you are already the smd_parent ninja, I will:

One of my category types is a location, which are child-categories of regions or continents. I tried and failed to get chh_article_custom to correctly handle the continents (the parent category) so in the end I designed that choice out of my select drop-downs by making the continent as non-selectable OPTGROUPs to make this failing less apparent. I suspect with some added MYSQL trickery one could get this functionality, but it requires checking cross-checking whether the current article’s category belongs to the parent in the query and I have no idea how to go about that. ? Any tips on how to build the SQL query or tips on combining your plugins?

Textpattern CMS

Textpattern CMS support forum

#49 2008-06-18 19:31:39

Re: smd_query: Talk to the database directly via SQL

#50 2008-06-18 19:52:04

Re: smd_query: Talk to the database directly via SQL

#51 2008-06-18 20:03:08

Re: smd_query: Talk to the database directly via SQL

#52 2008-06-19 12:16:12

Re: smd_query: Talk to the database directly via SQL

#53 2008-06-19 12:27:18

Re: smd_query: Talk to the database directly via SQL

#54 2008-06-19 12:47:39

Re: smd_query: Talk to the database directly via SQL

#55 2008-07-02 14:18:13

Re: smd_query: Talk to the database directly via SQL

#56 2008-07-02 14:31:37

Re: smd_query: Talk to the database directly via SQL

#57 2008-07-06 17:48:47

Re: smd_query: Talk to the database directly via SQL

#58 2008-07-06 18:24:42

Re: smd_query: Talk to the database directly via SQL

#59 2008-07-06 19:56:58

Re: smd_query: Talk to the database directly via SQL

#60 2008-07-06 21:35:07

Re: smd_query: Talk to the database directly via SQL

Board footer