Topic: SQL query defined by my users.

I am developing my first rails app and I am having a hard time trying to figure out something.

In my app, users have to be able to make sql queries directly.

I tough about having something resembling www.tryruby.org console, but i have no clue how to do it.

What I am doing now is, i have a form_for that sends the query to my sql controller, that will be making the validations and then return the result of the query on a partial to be rendered on the same page.
The problem is that my partial is rendering a new page, which is weird, because my controller should UPDATE the page, rendering the partial on a given DIV.

As soon as I am home I am gonna post some code.

Last edited by erzads (2011-07-13 09:52:57)

Re: SQL query defined by my users.

Part of your post is missing.  However, the first part is already scary... smile

Re: SQL query defined by my users.

view:
<% form_tag(url_for(:controller => "stages", :action => "sql"), :remote => true) do %>
  <%= text_field_tag :query %>
  <%= submit_tag "vai" %>
<% end %>

controller:
  def sql
    if (params[:query]).blank?
      @fase1 = Fase1.all
    else
      @fase1 = Fase1.find_by_sql (params[:query])
    end
    render :update => 'query_result', :partial => 'shared/sql_result'
    #render :update do |page|
      #page.replace "query_result", :partial => 'shared/sql_result'
    #end
  end

*That partial has just a simple string for testing purpose.
*My view has a div with id="query_result"

Last edited by erzads (2011-07-18 06:35:29)

Re: SQL query defined by my users.

Is this just an experimental app? Allowing users to write their own SQL query is both highly unusual and highly dangerous. Its not even recommended that you allow users to submit unfiltered parameters for a query that you define.

Normally, you will define the query yourself, as a detailed knowledge of the database structure is required for this, then allow users to submit parameters to qualify the query. Rails has techniques for ensuring that these parameters are safe to use in the query.

boomerang is right.

Re: SQL query defined by my users.

The idea is that people will practice SQL select commands on my app. So I am gonna have a set of databases for them to do some select commands.

I am yet to filter stuff and control all this. I am just trying to make it work first.

Re: SQL query defined by my users.

Its always best to make something work without Ajax first. You could easily have a problem with your jQuery or Prototype files.

I read somewhere that Prototype is being removed from Rails and that its best to deal with jQuery directly rather than remote='true'. I find that better. I think they realized that Rails just wants to hold your hand too much!

I'm probably going to get shot down in flames for this... (-:

Re: SQL query defined by my users.

Ok, but any tips on the main issue? I just wanna go to my controller, do something there and then come back to the same page I was, and update a partial.

Seems pretty standard but for some reason it keeps rendering a new page just for the partial.

Re: SQL query defined by my users.

up.

Forget the whole sql thing.

Can I update a partial after I submit a ajax form?

Re: SQL query defined by my users.

Got it, you can close this tread moderator.

I had to use a remote_form_for with :update, and instead of updating a partial on my controller I just had to render a partial. big_smile

Re: SQL query defined by my users.

Actually.. don't close it yet smile

I am trying to find ways to validate the query before running it.

Do you guys have ideas on ways to do that?

Re: SQL query defined by my users.

What do you mean by that?  If it's a select you can pretty safely run it and catch the exception if any.

Re: SQL query defined by my users.

How can I make it so my query only accepts SELECT queries?

Re: SQL query defined by my users.

It depends.  One option is to prepend the string with "SELECT" so that your users don't have to type it.  Another is to check if the first word is "SELECT" (has to be by standard SQL syntax).

Yet another approach would be to wisely open a separate connection to the DB for the query in the name of a user who only has select rights to the allowed tables/views in the RDBMS and let the RDBMS throw an exception if the user tries anything else.  This has many benefits on the security/control side of user activity, however it requires a bit of digging around stuff normally fully covered by ActiveRecord.

Re: SQL query defined by my users.

I was thinking about doing that, but I don't really know DB (or rails XD) well enough  but I am gonna look for it.

Thanks boomerang big_smile