Ruby with Sequel and Sinatra: form parameter passing

Question

I am new to Ruby and Sinatra. I am attempting to pass parameters from an HTML form and insert them into a PostgreSQL database (on Heroku) with Sequel.

The connection to the database works because I have succeeded with this code block

DB = Sequel.connect(connection_ credentials)

insert_ds = DB["INSERT INTO users (email_address, username, password) VALUES ('email@email.com', 'my_username', 'my_password')"]
insert_ds.insert

This works fine, but I cannot insert data from an HTML form. The data is not being passed.

So, for example, this does not work

@email_address = params[:email_address]
@username = params[:username]
@password = params[:password]

insert_ds = DB["INSERT INTO users (email_address, username, password) VALUES (@email_address, @username', @password)"]
insert_ds.insert

The error message is

Sequel::DatabaseError at / PG::Error: ERROR: column "email_address" does not exist LINE 1: ...sers (email_address, username, password) VALUES (@email_addr... ^

which leads me to presume that the parameter was not passed

The full code is:

require 'sinatra'
require "rubygems"
require "sequel"
require 'sinatra/sequel'
require 'pg'

DB = Sequel.connect('postgres://my username:my password@ec2-54-243-250-125.compute-1.amazonaws.com:5432/d70h0792oqobc')

get '/' do
  #@users = users.all :order => :id.desc
  #@title = 'All Users'
  erb :index
end

post '/' do
    @email_address = params[:email_address]
    @username = params[:username]
    @password = params[:password]
    insert_ds = DB["INSERT INTO users (email_address, username, password) VALUES (@email_address, @username, @password)"]
    insert_ds.insert
    redirect '/'
end


__END__

@@ layout
<!DOCTYPE html>
<html>
<head></head>
<body>
<%= yield %>
</body>
</html>

@@index
<section id="add">
  <form action="/" method="post">
  <label class="label"> <span>Email Address: </span> </label> <input type="text" id="email_address" name="email_address" />
  <label class="label"> <span>Username: </span> </label> <input type="text" id="username" name="username" />
  <label class="label"> <span>Password: </span> </label> <input type="password" id="password" name="password" />
    <input type="submit" value="Register me!">
  </form>
</section>

Very grateful for all help!

Thank you.

score 1 · Answer 1 · edited May 23 '17 at 10:32

Summary: Your code wasn't working because you were sending invalid SQL to the database, due to a misunderstanding of how Sequel works (and how Ruby's string interpolation works).

Details: You almost never need to (nor should you) write raw SQL code when using the Sequel library. To use Sequel more appropriately, do this:

DB[:users] << {
  email_address: params[:email_address],
  username:      params[:my_username],
  password:      params[:my_password]
}

or this:

DB[:users].insert( email_address:params[:email_address], … )

If you want to store these as instance variables for some reason (you're using them in a view response for the post?) then:

@email = params[:email_address]
@user  = params[:username]
@pass  = params[:password]
DB[:users] << { email_address:@email, username:@user, password:@pass }

If you really want to write raw SQL, you can use placeholders safely like so:

DB[
  'INSERT INTO users (email_address,username,password) VALUES (?,?,?)',
  params[:email_address],
  params[:username],
  params[:password],
]

The benefit of this is that it prevents users from performing a SQL Injection Attack on your site, for example by saying that their username is bob','haha'); drop table users; select('. (Think about what happens if you put that into a normal INSERT statement between two ' characters.)

Since your form parameters match your column names, you can even use a custom function for slicing your hash to make this even easier, e.g.

DB[:users] << params.slice(:email_address,:username,:password)

Read the Sequel Documentation (e.g. the Cheat Sheet) for more information on how to properly use it.

For the Future:

Your problem was unrelated to Sinatra or parameters, but simply how you were incorrectly using Sequel. For the future, to test if you are getting your parameters, you can do:

p params

and look at your console (I assume you are developing locally?) or else use whatever logging capabilities Heroku gives you if you are developing live on the server (*shudder*). Alternatively, you can even do:

post '/' do
  params.inspect
end

and you will see, when you post the form, exactly what is in the params hash. Then you won't apply blame and investigation to the wrong area.

I was wondering the same thing. Perhaps because of a perceived "RTFM" style part in the middle? — Phrogz, May 09 '13 at 01:14
@user1903663 You're welcome. Note that if you feel that this has fully answered your question, you should [accept it](http://meta.stackexchange.com/a/5235/153741) by clicking on the checkmark next to the answer. — Phrogz, May 09 '13 at 02:20
Thank you for an amazingly full analysis of my errors and the proposed solutions, I am very grateful. I note particularly what you say about "sql injection". I have learnt something important here. — user1903663, May 09 '13 at 02:40

score 1 · Accepted Answer · answered May 09 '13 at 00:58

1

You are trying to interpolate the global variables @email_address, @username, and @password but haven't used the interpolation operator #

Your SQL string apears as

INSERT INTO users (email_address, username, password) VALUES (@email_address, @username', @password)

when (apart from the stray single quote) you mean to have the values of those variables appear within the command. You should write instead

    insert_ds = DB["INSERT INTO users (email_address, username, password)
                               VALUES (#@email_address, #@username, #@password)"]

It is easy to diagnose this by adding

puts insert_ds

directly after the assignment.

answered May 09 '13 at 00:58

Borodin

126,100
9
70
144

1

+1 for correctly identifying the source of the problem, but -1 because directly taking user input to construct SQL statements via string interpolation is exactly the sort of thing that gets websites owned via SQL injection attacks. – Phrogz May 09 '13 at 01:06
@Phrogz: To an extent I agree. But not knowing `Sequel` and how (and whether) to use placeholders I left it as a working hack. Validating user input is beyond the scope of this question. – Borodin May 09 '13 at 01:10
thank you both contributions, very helpful. I am sorry if it seems so simple. I am coming form PHP background and the adjustment is difficult. – user1903663 May 09 '13 at 01:28
1

@user1903663: The mistake is forgiveable. I just don't understand why you couldn't debug it by displaying the contents of `insert_ds` before executing it. – Borodin May 09 '13 at 03:01

Ruby with Sequel and Sinatra: form parameter passing

2 Answers2