smd_query: Talk to the database directly via SQL

Bloke · 2008-05-23 22:52:28

Wheeeeeeeeeeeee. Had to continue my little monologue and post this example. I’ve just tried a slight variation of the plugin help’s Example 5 out in the files form.

Say you have downloads for artists on your record label. You list the latest download for each artist on a page (the files could be local or remote). What if you’d like to offer some further reading links to your articles about each band in case visitors land on the downloads page and like what they hear? No problem. Make your files form read:

<txp:text item="file" />: 
<txp:file_download_link>
<txp:file_download_name /> [<txp:file_download_size format="auto" decimals="2" />]
</txp:file_download_link>
<br />
<txp:text item="category" />: <txp:file_download_category /><br />
<txp:text item="download" />: <txp:file_download_downloads />
<br />
<txp:text item="Related articles" />:
<txp:smd_query query="SELECT DISTINCT
     txp.id, txp.title FROM textpattern AS txp
     WHERE (txp.keywords LIKE '%,?category%,'
     OR txp.keywords LIKE '%?category%,'
     OR txp.keywords LIKE '%,?category%'
     OR txp.category1 = '?category'
     OR txp.category2 = '?category')
     GROUP BY txp.title LIMIT 4"
     wraptag="ul" break="li">
  <txp:permlink id="{id}">{title}</txp:permlink>
</txp:smd_query>

Looks horrific but it’s not that bad. All it’s saying is:

for each file in your <txp:file_download_list />, grab its category (the band name in this case)
look at all the articles in the database and see if any of them have a matching category1 OR category2 OR have keywords that list the band’s name
if so, add the article to a list of other permlinked articles under the download details (up to a maximum of 4)

[ Extension homework: order the articles by posting date so it always shows the 4 most recent articles about that band ]

Sorry, couldn’t resist. I’ll get back in my box now.

jm · 2008-05-23 22:53:18

D’oh! I had a trailing slash. Containers work great. Here’s my little example:

Top commenters:

<txp:smd_query break="li" query="select count(*) as total,
    name from txp_discuss
    group by name
    order by total desc
    limit 10" wraptag="ol">{name}: {total}</txp:smd_query>

Bloke · 2008-05-23 22:55:22

jm wrote:

Top commenters

Niiiiiiiiiiice!

jm · 2008-05-23 23:23:57

I couldn’t help myself – I’ve got another one, though it’s only useful for jmd_dashboard.

Unmoderated comments (with edit link):

<h2>Unmoderated comments</h2>
<txp:smd_query break="li" query="SELECT discussid, name, parentid, 
    unix_timestamp(posted) as Posted
    FROM txp_discuss 
    WHERE visible = 0 
    ORDER BY posted DESC" wraptag="ul">
    <txp:jmd_dashboard_edit id="{discussid}" type="comment">
        {name} about <txp:php>echo since({Posted});</txp:php>
    </txp:jmd_dashboard_edit>
    on <txp:article_custom id="{parentid}">
        <txp:permlink><txp:title/></txp:permlink>
    </txp:article_custom>
</txp:smd_query>

Edit: Added article link

Last edited by jm (2008-05-23 23:26:52)

merz1 · 2008-05-24 23:16:59

Ouch. What’s going on here? This thread/plugin should not be available for the ~~public~~ newcomers :)

Very geeky little Pandora box. CMS abstraction layer made obsolete. The one tag CMS :)

What about the latest (search) referrers for an article, a site… ARGHHH…

Sheru · 2008-06-09 10:02:17

Greetings! Dear Stef, Thank you for great plugin smd_slimbox, I am enjoying with this plugin. Wonderful work…
I am even now trying another smd_query plugin to read files..

I have differnt categories files such as
ATC-Codes |Other Documentation |Example data |Macintosh |Windows. All data is read from txp_files.

I now wish to add a search box above this category to search the files from category or other fields.

Problem:
Once I click on Search button, how should I track where to take those.. No idea.
I am trying much to make it work but achieved no success..

Could please help me to make it work.
Thank you a lot for your kind help.
Sheru

Last edited by Sheru (2008-06-09 10:06:21)

Bloke · 2008-06-09 14:12:33

Sheru wrote:

I now wish to add a search box above this category to search the files from category or other fields.

As it stands right now, smd_query can’t be used for doing that. I deliberately avoided putting any ability to read from the URL into the plugin because I thought it might open a potential security hole in the otherwise stable TXP.

However, since the semi-unofficial release of smd_vars it is currently possible (indirectly) to do what you want by storing the query in smd_vars. Because of this, I am warming to the idea of enabling URL support directly in this plugin.

There are two schools of thought:

it’s a bad idea because any visitor could potentially perform an SQL injection attack if the query was written in such a way; thus the function should be disallowed
why should I stand in the way of limiting your creativity; it is up to you to make sure you have taken all necessary steps to minimise security risks

I’m not sure which camp I’m in. It’s possibly unreasonable to expect everyone using TXP to be able to write a secure SQL query but then TXP users are all pretty damn smart so I’m caught in this moral dilemma. If it only impacted the plugin I’d have no trouble invoking the ability. But since smd_query is by its nature completely open to the whole database — and beyond — I’m not sure it’s such a good idea.

If I could guarantee I was using all the correct safety mechanisms that TXP offers (url escaping / slashes / stripping out code or disallowed characters etc) then I’d move towards #2, but as it stands I don’t trust my code enough to allow people to unleash its full potential. I have tried putting doSlash() around all the input, but that generates SQL errors all the time because it changes

select * from textpattern where title like 'test'

into

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

which throws up an error.

Perhaps if somebody more knowledgeable in security zen could advise what I’m currently missing to make sure the queries have a good chance of not receiving bogus input, I’d be more inclined to enable it.

Anyone?

uli · 2008-06-09 14:48:05

Stef, I once was a user of a keyboard shortcut application that was capable of irrevocably deleting files from the HD. The shortcut codes had to be handwritten. In order to confirm that I was aware of what the code I was about to type could cause potentially, I had to put a kind of suffixing term on the same line: “dangerous”. Would it satisfy the critical halfth of you if you would mention where and why there are potential security holes and how to avoid them, and one had to confirm any dangerous action by putting a certain, perhaps less absurd, keyword into any smd_tag involved?
Moreover: could such a keyword put smd_query in a more secure mode, f.i. making access to tables or databases impossible, that are not mentioned in another smd_var?

Last edited by uli (2008-06-09 14:56:53)

Bloke · 2008-06-09 14:59:51

uli wrote:

Would it satisfy the critical halfth of you if you would mention where and why there are potential security holes and how to avoid them, and one had to confirm any dangerous action by putting a certain, perhaps less absurd, keyword into any smd_tag involved?

Not a bad idea. Sort of an “I know what I’m doing” switch. I’d still like to know how to harden it generally, i.e. which of the core functions should I be calling to escape user input, but the idea of a “dangerous mode on” switch is certainly appealing, thanks :-)

Moreover: could such a keyword put smd_query in a more secure mode, f.i. making access to tables or databases impossible, that are not mentioned in another smd_var?

Again, a great idea. If it’s possible I’ll look into this sort of system and see if it has any legs.

Nice one!

Sheru · 2008-06-10 08:07:05

Warm Greetings, Dear Stef, Thank you so much for your great reply and kind help.

Stef Wrote

However, since the semi-unofficial release of smd_vars it is currently possible (indirectly) to do what you want by storing the query in smd_vars.

As you mention about SQL Injection on smd_query, How far is smd_vars safe to use ?

If smd_vars safe, Could you please help me on this.?

Once Again thank you.
Sheru

Last edited by Sheru (2008-06-10 08:13:08)

Bloke · 2008-06-10 10:25:21

Sheru wrote:

As you mention about SQL Injection on smd_query, How far is smd_vars safe to use?

It’s “safe” in the sense that anything you store in the smd_vars table is well away from the core tables so you can add and remove stuff to your heart’s content. The “unsafe” part is that you are of course taking user input from an untrusted source (users on the Internet) and putting those values directly into an SQL query. If those users are clever (malicious) or make typos in the search box there are not many safeguards at the moment. I guess the plugin will produce ugly errors if the input contains quote characters or other strangeness. The good news is that because the query will use the same value twice it is perhaps harder to inject bad data into it without making a malformed query.

When I hear from people who know more than me about what I should be doing to minimise the risks I’ll build them in to make it safer. At the moment I don’t recommend using this on a public-facing site, but by all means put it on a hidden/test part of the site to try it out. Hopefully in the next few weeks I’ll have the info I need to make smd_query work better… and then you might not even need smd_vars at all!

The other thing to note is that smd_vars is very early in its development and I have a lot of ideas on how to expand it. I will of course try to minimise changes to the tags now so stuff doesn’t change too drastically and cause much extra effort on the part of site designers. But I can’t guarantee that the attributes won’t change at this stage.

If all that has not put you off then it’s fairly simple to do what you want, but it ultimately depends on what you wish to offer when you say “other fields” and how clever you want to be.

For files I would guess the key things to search would be category and description (possibly filename but we’ll leave that out for now).

So what you do is put your (X)HTML search form on a page/article and direct the HTML <form>‘s action parameter to the same article. Then you will need some conditional logic at the top of the page to detect if the user has submitted the form or not.

Alternatively, you can code the destination URL as a special article (e.g. domain.com/info/search_results), but I would suggest doing it on the same page for reasons that will become apparent in a moment. Either way, your HTML form should contain a text box with an appropriate name (e.g. “criteria”) then your page needs to do four things:

Check that the data is present in the URL
Retrieve the query and (automatically) plug the data into it
Perform the query
Display the results

First of all, go to the Extensions->Variables tab and add a new variable named files_query with this as its Value:

SELECT * from txp_file
         WHERE description like '%?criteria%'
         OR category like '%?criteria%'

Once you’ve added it, that becomes your SQL template for this particular query.

Then, on your page with the HTML form on it, we’ll use smd_if at the top because it can check for the existence or not of certain variables. This is untested but something along these lines will get you started:

<txp:smd_if field="urlvar:criteria" operator="isused">
  <txp:smd_query query="<txp:smd_var_get name="files_query" />"
        form="show_results" wraptag="table" break="tr"
        label="Search results" labeltag="h3" />
<txp:else />
  //  Display the page content and search box here so visitors can try again
</txp:smd_if>

(you will probably need to wrap smd_query with the asy_wondertag plugin if you’re not using the latest SVN).

In this case you do not need to bother to inject replacement variables because the ‘?’ fields will be replaced for you automatically from whatever is submitted by the site visitor.

Then your search_results form could contain this to tabulate the returned matching files:

<td><txp:file_download_link id="{id}">{filename}</txp:file_download_link></td>
<td>{category}</td>
<td>{description}</td>
<td>downloads: {downloads}</td>

See if that works for starters and then you can expand on it from there.

Last edited by Bloke (2008-06-10 10:28:33)

Sheru · 2008-06-10 12:53:34

Warm Greetings from Sheru, Thank you so much Stef for heaps of information and great explanation. I have learnt things with you. Thanks for your precious time for me. Hope, We Txp Community get more resolved version of smd_query plugin very soon. For this Best of Luck.

Stef Wrote

For files I would guess the key things to search would be category and description (possibly filename but we’ll leave that out for now).

Yes, I would search files by category

I have few questions..(Sorry if my question is too simple)..

Stef Wrote
bq. First of all, go to the Extensions->Variables tab and add a new variable named files_query with this as its Value:

I did not find variable under Extensions tab. Where is it?

Stef Wrote

(you will probably need to wrap smd_query with the asy_wondertag plugin if you’re not using the latest SVN).

I am using 4.0.6 version. Do I need to wrap smd_query?

One more time thank you so much for your kind help
I will come in touch with you.
Sheru

Textpattern CMS

Textpattern CMS support forum

#25 2008-05-23 22:52:28

Re: smd_query: Talk to the database directly via SQL

#26 2008-05-23 22:53:18

Re: smd_query: Talk to the database directly via SQL

#27 2008-05-23 22:55:22

Re: smd_query: Talk to the database directly via SQL

#28 2008-05-23 23:23:57

Re: smd_query: Talk to the database directly via SQL

#29 2008-05-24 23:16:59

Re: smd_query: Talk to the database directly via SQL

#30 2008-06-09 10:02:17

Re: smd_query: Talk to the database directly via SQL

#31 2008-06-09 14:12:33

Re: smd_query: Talk to the database directly via SQL

#32 2008-06-09 14:48:05

Re: smd_query: Talk to the database directly via SQL

#33 2008-06-09 14:59:51

Re: smd_query: Talk to the database directly via SQL

#34 2008-06-10 08:07:05

Re: smd_query: Talk to the database directly via SQL

#35 2008-06-10 10:25:21

Re: smd_query: Talk to the database directly via SQL

#36 2008-06-10 12:53:34

Re: smd_query: Talk to the database directly via SQL

Board footer