Textpattern CMS support forum

You are not logged in. Register | Login | Help

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

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

Re: smd_query: Talk to the database directly via SQL

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:

  1. 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
  2. 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?


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

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

uli
Moderator
From: Cologne
Registered: 2006-08-15
Posts: 4,205

Re: smd_query: Talk to the database directly via SQL

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)


In bad weather I never leave home without wet_plugout, smd_where_used and adi_form_links

Offline

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

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

Re: smd_query: Talk to the database directly via SQL

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!


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

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

Sheru
Member
From: Kathmandu, Nepal
Registered: 2007-05-09
Posts: 96

Re: smd_query: Talk to the database directly via SQL

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)

Offline

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

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

Re: smd_query: Talk to the database directly via SQL

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:

  1. Check that the data is present in the URL
  2. Retrieve the query and (automatically) plug the data into it
  3. Perform the query
  4. 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)


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

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

Sheru
Member
From: Kathmandu, Nepal
Registered: 2007-05-09
Posts: 96

Re: smd_query: Talk to the database directly via SQL

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

Offline

#37 2008-06-10 13:01:08

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

Re: smd_query: Talk to the database directly via SQL

Sheru wrote:

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

Sorry, you have to install and activate the smd_vars plugin first :-) Then when you go to the Extensions tab you’ll see a ‘Variables’ sub-tab. There you will find an “Install table” button. Click it. Then you will have the ability to add variables to textpattern as I wrote above.

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

Yes, more than likely. If you try it and you get errors or odd " and > characters appearing in the output, install the asy_wondertag plugin and do:

<txp:asy_wondertag>
  <txp:smd_query ........ />
</txp:asy_wondertag>

That should help.


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

#38 2008-06-11 04:21:22

Sheru
Member
From: Kathmandu, Nepal
Registered: 2007-05-09
Posts: 96

Re: smd_query: Talk to the database directly via SQL

Warm Greetings from Sheru, Dear Stef, Thank you a lot for your answers and for your precious time. I have received huge information on smd_query and smd_vars and it’s implementations on above. I will now try to use it. I will come in touch with you.

Thank you so much for your kind help.
Sheru

Offline

#39 2008-06-11 08:07:30

Sheru
Member
From: Kathmandu, Nepal
Registered: 2007-05-09
Posts: 96

Re: smd_query: Talk to the database directly via SQL

Dear Stef, I am stuck..

Stef Wrote
bq. 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.

<txp:asy_wondertag>
<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 
// I have added form this way
<form action="../downloads" method="get">
<tr><td>Search<td /><td><input type="text" name="criteria" value="criteria" /><td /><td><input type="submit" name="Search" value="Search" /><td /><tr />
</form>
</txp:smd_if>
</txp:asy_wondertag>

- I have added the query on Extensions>Variables tab.
- Added the show_results page

When clicked Search button, following url displays
http://localhost:8888/testsite/downloads/?criteria=doc&Search=Search

And does not display results. Remains on the same page.
Could not figure out.

Could you please help me?
Thank you so much for your kind help
Sheru

Last edited by Sheru (2008-06-11 08:08:46)

Offline

#40 2008-06-11 09:19:58

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

Re: smd_query: Talk to the database directly via SQL

Hmm, try this:

<txp:smd_if field="urlvar:criteria" operator="isused">
 <txp:asy_wondertag>
  <txp:smd_query query="<txp:smd_var_get name="files_query" />"
        form="show_results" wraptag="table" break="tr"
        label="Search results" labeltag="h3" />
 </txp:asy_wondertag>
<txp:else />
  // Display the page content and search box here so visitors can try again 
// I have added form this way
<form action="../downloads" method="get">
<tr><td>Search<td /><td><input type="text" name="criteria" value="criteria" /><td /><td><input type="submit" name="Search" value="Search" /><td /><tr />
</form>
</txp:smd_if>

The asy_wondertag might be preventing smd_if (or the rest of the page) from working. It is best to only use asy_wondertag around the one tag that causes problems.

If that does not work, verify that it is definitely calling smd_if by adding debug="1" to it. You should see a load of diagnostic output. Check that the urlvar is being read properly and tested. Post the results of the debug if you can’t figure out what it means.


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

Board footer

Powered by FluxBB