smd_query: Talk to the database directly via SQL

Bloke · 2008-07-07 08:55:49

jakob wrote:

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?

I think pretty much anything that isn’t a-z, A-Z, 0-9, a dash or an underscore. Think that’s all the category (name) accepts.

Would this actually serve as a general solution?

Yes, though it depends on the field used, which is why I haven’t employed it yet. For stuff on the URL line, someone could be using a query to search for stuff in an article so to filter out semicolons and non-alphanumeric characters may not be desirable. But I think stripping out HTML/PHP tags using strip_tags(), as well as perhaps semicolons might harden it enough for general use, so I may employ this in the plugin.

Another option I’m exploring is being able to define what you deem “acceptable” on a per-query basis. Not sure if that’ll work, or if the code would be too ugly but it’s a possibility.

Any quick idea on how best to do this?

I think you’d need to use PHP/another query unfortunately. There’s possibly a plugin opportunity here :-) You could:

$num = safe_count("txp_category", "name='".$_GET["c"]."'");

and if $num is > 0, you know the category exists.

Is the form processed at all when there are no hits in smd_query?

Aha! You found the flaw in my argument; the form is not processed if there are no hits, d’oh! I’ll definitely have to address that in the next version. Will see if I can make some time this week for it.

…whether the current article’s category belongs to the parent in the query

I’m not quite sure I follow. I can see the logic behind the OPTGROUP, so are you saying that if the user somehow circumvents this and submits, say, site.com/article&c=continents that you want the SQL to detect this is a “parent”? What would you want it to do in this eventuality? Return nothing (since the query is “not valid”) or show the first location? Or something else?

If you let me know a bit more detail on what you want to do I can probably figure out some way of doing it, but I’m not sure if it’s possible directly with SQL unless you hard code the exceptions into it (e.g. AND category != 'continents'). Some PHP trickery might work.

wet · 2008-07-07 09:00:04

$num = safe_count("txp_category", "name='".$_GET["c"]."'");

Don’t do that. All user input is tainted.

jakob · 2008-07-07 09:24:20

wet wrote:

bq. $num = safe_count("txp_category", "name='".$_GET["c"]."'");

Don’t do that. All user input is tainted.

meaning that this in itself is open to abuse?

Would another way be more appropriate and safer, e.g. reading out the categories into an array, then comparing with the GET variable?

If you let me know a bit more detail on what you want to do I can probably figure out some way of doing it

Because I couldn’t get it to work previously I ‘designed out’ the ability to list by parent category. I was hoping I could restore that functionality, i.e. that one can list articles by parent category too. As the parent category isn’t one of the fields in the textpattern table one would need to cross-reference against the txp_category table to ascertain if an article in the textpattern table belongs to a child category of the parent one is searching for. Dunno if that’s any clearer?

wet · 2008-07-07 09:54:41

jakob wrote:

meaning that this in itself is open to abuse?

Of course. Unfiltered use of $_GET variables is the classic way of introducing SQL injection backdoors. Textpattern contains doSlash() and gps() as a filter, open for Plugin authors to use. Look through some of TXP’s source to get an idea of how to properly handle user input.

Bloke · 2008-07-07 10:06:30

wet wrote:

$num = safe_count("txp_category", "name='".$_GET["c"]."'");
Don’t do that. All user input is tainted.

*cough* yes, I neglected to mention that should go hand in hand with your ‘checking’ if the category itself exists and also copied your code up and forgot to edit it sufficiently. Sorry. I’m saying one thing about security and then using the wrong damn variable in my examples = dickhead. Forget I exist.

jakob wrote:

As the parent category isn’t one of the fields in the textpattern table one would need to cross-reference against the txp_category table to ascertain if an article in the textpattern table belongs to a child category of the parent one is searching for.

Gotcha, I think. Will give it some thought when I get a moment and, perhaps, I’ve woken up enough to be of value.

Last edited by Bloke (2008-07-07 10:07:29)

merz1 · 2008-07-07 13:01:00

Stef: A little off-topic (now) but maybe something for the future:

One of my category types is a location, which are child-categories of regions or continents.

That would be one possible good usage of the tag1:tag2:tag3 hierarchy mentionend in the tru_tags thread.

jakob · 2008-07-09 10:37:21

robert, thanks for the pointers. Would it be sufficient then to replace:

<txp:php>echo $_GET["c"];</txp:php>

with

<txp:php>echo doslash(gps("c"));</txp:php>

?

Is the form processed at all when there are no hits in smd_query?

Aha! You found the flaw in my argument; the form is not processed if there are no hits, d’oh! I’ll definitely have to address that in the next version. Will see if I can make some time this week for it.

Regarding detecting whether there are no hits I thought I could perhaps use <txp:variable name="results" value="yes" /> in the form to set a variable when the form is processed. If when there are no results the form is not processed then that variable would not be set. Afterwards I can test to see if it exists and output a message if not set:

<txp:if_variable name="results" value="yes">
<txp:else />
no results message
</txp:if_variable>

Problem is, it doesn’t work as expected. The tag trace always registers as false regardless of whether there are hits or not:

Tag trace excerpt:
			<txp:if_variable name="results" value="yes">
				[<txp:if_variable name="results" value="yes">: false]
			</txp:if_variable>

Could this have something to do with the order in which txp processes things? In the tag trace the loop through the form in which the variable “results” is set comes in the [ ~~~ secondpass ~~~ ] after the if statement. Any ideas?

Bloke · 2008-07-14 16:47:25

Not the big update everyone hoped, but a small leap forward at least.

v0.12 [ compressed ] adds:

<txp:else /> support for when the query returns no rows (thanks jakob)
silent attribute to suppress error messages caused by invalid queries
Three new replacements:
- {smd_rows} : total number of rows returned
- {smd_thisrow} : current row number
- {smd_thisindex} : current row number (counting from zero)
count attribute to determine whether to count the rows up or down

With that lot you should be able to do some cool if/else scenarios as well as employ smd_if to test for row numbers.

Have fun.

jakob · 2008-07-14 21:06:05

Hey, Stef, that’s excellent news. Unfortunately my query above returns a Tag error: <txp:else /> -> Textpattern Warning: tag does not exist on line 1076. I’ve checked the syntax (e.g. no closing /> on smd_query) and I’ve tried using it with a form as well as a container. The ensuing error mentions if_section:

textpattern/publish.php:1076 trigger_error()
textpattern/publish.php:984 processTags()
textpattern/lib/txplib_misc.php:1511 parse()
textpattern/publish/taghandlers.php:245 parse_form()
textpattern/publish.php:1062 output_form()
textpattern/publish.php:984 processTags()
textpattern/publish/taghandlers.php:2875 parse()
textpattern/publish.php:1062 if_section()
textpattern/publish.php:997 processTags()
textpattern/publish/taghandlers.php:2442 parse()

Do you think the plugin is having a problem with nested txp:else’s? It didn’t seem to mind the <txp:else /> in the <if_variable of the form zebra striping but I tried removing that too to narrow down the factors and still got the same error.

BTW: in your else example in the help you need an xml-style /> for txp:else.

Finally, what exactly does silent silence? would it silence other messages like the no txp:article on this page?

Bloke · 2008-07-14 21:33:44

jakob wrote:

Hey, Stef, that’s excellent news. Unfortunately my query above returns a Tag error: <txp:else /> -> Textpattern Warning: tag does not exist on line 1076.

Nuts, I had that error earlier today but thought I’d squashed it :-( I think you’re right about the nested txp:else tags. I’ll try an example with some nested tags and report back. FYI, it’s really a cheating way of adding else support because it does what txp:if_different does and reads the content of the “true” branch first, and only executes the ‘false’ part if there’s no results. A true if/else would do it differently (perhaps I need to do that, but it was a lot of extra code)

~~Actually, this might be a TXP 4.0.6 parser issue. Just realised I never tested it on a stock 4.0.6.~~ [ EDIT: I’m talking crap, you’re using txp:variable so must be on SVN. ] I’m definitely having one of those days. Think I’ll go to bed and forget today ever existed.

BTW: in your else example in the help you need an xml-style /> for txp:else.

Thanks for that. I’ll fix it when I fix the above mess!

Finally, what exactly does silent silence? would it silence other messages like the no txp:article on this page?

I don’t think so, it only silences the query (just adds an ‘@’ in front of the call) so any bogus params silently cause the query to fail instead of giving an ugly error. The “no txp:article” message is part of the core TXP and I don’t think can be circumvented. I’m open to ideas if anyone has any.

Last edited by Bloke (2008-07-14 21:59:36)

jakob · 2008-07-14 22:04:41

Thanks for the rapid feedback (as always!). Yes, I’m on an svn version and yes, I’ll eagerly test your txp:else revisions :-)

Bloke · 2008-07-14 22:59:22

Sorry jakob, I can’t make it fail with any errors unless I do some horrible stuff with quotes (like put single quotes inside single quotes or mis-match them). Can you post your exact setup again please (the example above I assume you refer to doesn’t have an else part so I’m guessing what you’re doing).

I’ve tried this. It’s a little more hard-coded than yours, for expedience, but the sentiment is the same I think:

In an article: <txp:output_form form="jakob" />

In form jakob:

<txp:variable name="sqlmatch" value=" AND (Category1 = 'Business')" />

<txp:smd_query
      query='SELECT ID, Title, Category2 FROM textpattern
          WHERE Status >= "4"
          AND Posted < now()
          <txp:variable name="sqlmatch" />'
      wraptag="table">
  <!-- smd_query container -->
  <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>{ID}</td>
    <td><txp:category name="{Category2}" title="1" link="1" /></td>
    <!-- nested if/else -->
    <txp:if_section name="article">
      <td>It's an article</td>
    <txp:else />
      <td>It's something else</td>
    </txp:if_section>
  </tr>
<txp:else />
  <!-- smd_query else part, with a nested if/else -->
  <txp:if_section name="article">
    <p>Nothing to see here</p>
  <txp:else />
    <p>There would be something here but it takes time to write</p>
  </txp:if_section>
</txp:smd_query>

That executes fine and does what I’d expect. If I mess around with making the query invalid (e.g. changing category1 to one that doesn’t exist), the ‘else’ is executed and I get Nothing to see here displayed. If I change the if_sections to some other section name, the nested else-inside-the-else is executed.

If I completely destroy the query (by changing the table name to “txtptrn” for example) I get an error message about an invalid query. Using silent="1" suppresses this. In both silent and non-silent cases, smd_query’s “else” is executed and the appropriate message is printed. When everything checks out I get a lovely table with the articles tabulated and a cell added to the end with text in it that depends on the section from which the form is called.

In short, I can’t break it :-)

Please tell me what I’m doing wrong (or right!) so I can track down the problem you have. Thanks in advance.

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

Textpattern CMS

Textpattern CMS support forum

#61 2008-07-07 08:55:49

Re: smd_query: Talk to the database directly via SQL

#62 2008-07-07 09:00:04

Re: smd_query: Talk to the database directly via SQL

#63 2008-07-07 09:24:20

Re: smd_query: Talk to the database directly via SQL

#64 2008-07-07 09:54:41

Re: smd_query: Talk to the database directly via SQL

#65 2008-07-07 10:06:30

Re: smd_query: Talk to the database directly via SQL

#66 2008-07-07 13:01:00

Re: smd_query: Talk to the database directly via SQL

#67 2008-07-09 10:37:21

Re: smd_query: Talk to the database directly via SQL

#68 2008-07-14 16:47:25

Re: smd_query: Talk to the database directly via SQL

#69 2008-07-14 21:06:05

Re: smd_query: Talk to the database directly via SQL

#70 2008-07-14 21:33:44

Re: smd_query: Talk to the database directly via SQL

#71 2008-07-14 22:04:41

Re: smd_query: Talk to the database directly via SQL

#72 2008-07-14 22:59:22

Re: smd_query: Talk to the database directly via SQL

Board footer