Posts tagged with sql - page 2

Getting started with Ruby on Rails on Mac OS X

Choosing your Ruby packages

I have decided to get to grips with Ruby on Rails and get it set-up on my MacBook Pro. There seem to be a variety of options available:

  1. Use Locomotive – an all-in-one standalone developer package
  2. Official sources – configure, compile and install each
  3. MacPorts – package management for free software
  4. Fink – package management for free software

As Scott Stevenson points out this is too many options for a system that is described as Convention over configuration.

As I already use Fink for installing the majority of free software on my Mac I decided I’d go with that. At least until Leopard ships with Ruby on Rails in-the-box.

Preparing Fink

GrinGod gave me a heads-up that we’ll need to turn on the unstable packages to get the gems package and latest version of Ruby. The details are in the Fink FAQ but in summary:

fink configure

And choose the option for unstable trees. Then type:

fink selfupdate
fink index
fink scanpackages

And make sure you have rsync or cvs updating turned on to get the descriptions.

Ruby (via Fink)

Ruby is the language we wish to use and Fink currently supports 1.6 and 1.8 The following commands will install 1.8 for us and the Ruby Gems package management system:

fink install ruby
fink install rubygems-rb18

Rails (via Gem)

Now we need the Rails web framework which will be installed using gem.

gem install rails --include-dependencies

Choosing your web server

There are a number of options available again here including Apache, LightTPD and Mongrel. I wanted to give LightTPD a shot as it is on Fink and less of a beast than Apache. I tried various combinations but even having installed the lighttpd-fastcgi module I couldn’t get the fastcgi gem to do anything but spew errors so Mongrel it is.

Mongrel (via Gem)

Back at that Terminal window type:

sudo gem install mongrel --include-dependencies

Select the mongrel 1.0.1 (ruby) and fastthread 1.0 (ruby) options as prompted.

Testing Ruby, Rails & Mongrel

Again at the Terminal window:

rails mytest

A number of files should be created if Ruby & Rails are working correctly. Now type:

cd mytest
script/server

You should see a line stating Starting Mongrel listening at 0.0.0.0:3000

Open your browser of choice and navigate to http://localhost:3000

Choosing a database

You can go with MySQL, PostgreSQL or SQLite from Fink alone. I went with MySQL for a change.

MySQL 5.0

Whilst Mac OS X Server comes with MySQL the standard edition does not. Rather than use the Fink port I went with the official MySQL 5.0 package primarily to get the preferences pane that allows me to start and stop it as required however it also includes a helpful monitoring widget.

To install download and run the MySQL…x86 package.

Copy MySQL.prefPane to /Library/PreferencePanes and re-open System Preferences. Head into the new MySQL pane and start MySQL up.

MySQL Tools

To manage MySQL and download the Mac OS X version of the GUI management tool.

Run the MySQL Administrator application and connect to localhost on the default port of 3306 with the user name root and a blank password. Don’t forget to change the password by heading over to the Account pane.

Ruby with MySQL

Install the native Ruby to MySQL library to improve db performance by heading back to the Terminal window and executing:

sudo gem install mysql -- --with-mysql-config=/usr/local/mysql/bin/mysql_config

Select the option for mysql 2.7 (ruby)

Testing the database

As MySQL doesn’t include a sample database head into MySQL Administrator and into the Catalogs pane. Create a new database by pressing the + button and giving it the name mytest.

Select mytest from the left then choose Create Table… from the Table Actions drop-down list.

Enter the table name products then use the + button to create the following columns:

Name Type Primary Key? Nullable? Auto Increment?
id INT Y N Y
name VARCHAR(255) N N N

Click Apply, Execute, OK then Close.

Go to the Terminal window and cd into the mytest folder you generated earlier.

Edit the config/database.yml file using nano or TextEdit and change the database name from mytest_development to mytest. You will also need to enter the password here – either for your root account or ideally a special application-specific account you set-up in MySQL Administrator.

Back at the trusty Terminal execute:

script/generate scaffold product
script/server

Now open your browser to http://localhost:3000/products/ and try creating, editing and deleting some products.

Next steps

And of course trying to find an editor/IDE that supports Ruby as Xcode certainly doesn’t and TextMate costs money (I’m happy to pay for things when I’m committed to the tech – not before)

[)amien

Parameterising the IN clause of an SQL SELECT in .NET

I’m a fan of parameterized queries with a strong dislike for building SQL (or other magic strings). Encoding, escaping errors, localization formatting problems and injection can run rampant when you think everything is a string.

Problem

Today I found myself a quandary as I needed to SELECT records based on a list of values I had. e.g.

SELECT * FROM Products WHERE ProductCode IN ('ABC123', 'DEF456', 'GHI789')

At first glance the relevant parameterized version might look like:

SELECT * FROM Products WHERE ProductCode IN (@productlist)

The problem here however is that if you put a comma-separate list of items into a string parameter named @productlist then it sends this to the database server:

SELECT * FROM Products WHERE ProductCode IN ('ABC123, DEF456, GHI789')

That’s not what we want at all.

This hack isn’t pretty and it has some limitations:
  • Only works with named parameters
  • Could upset some DB providers that don’t like having command parameters removed or command text modified
  • Parameter name to replace must be totally unique – i.e. not exist as a subset of another parameter name
  • Only preserves the basic IDbCommand declared properties

It should however work across DB providers and types.

Usage

The previous example would mean we use exactly the expected parameterized version with @productlist in place.

Add the parameter as you’d expect but instead of assigning a string/numeric to it assign something IEnumerable.

Finally call this method against the command and parameter before you execute it for the ‘magic’ to happen:

The ‘magic’

public void ExpandDbArrayParameter(IDbCommand cmd, IDbDataParameter parameter) {
  if (parameter.Value is IEnumerable) {
    int index = 0;
    StringBuilder newParameterSQL = new StringBuilder();
    foreach(Object value in (IEnumerable) parameter.Value) {
      String valueParameterName = String.Format("{0}{1}", parameter.ParameterName, ++index);
      IDataParameter valueParameter = cmd.CreateParameter();
      valueParameter.DbType = parameter.DbType;
      valueParameter.Direction = parameter.Direction;
      valueParameter.ParameterName = valueParameterName;
      valueParameter.SourceColumn = parameter.SourceColumn;
      valueParameter.SourceVersion = parameter.SourceVersion;
      valueParameter.Value = value;
      cmd.Parameters.Add(valueParameter);

      if (index == 1)
        newParameterSQL.Append(valueParameterName);
      else
        newParameterSQL.Append("," + valueParameterName);
      }
      cmd.Parameters.Remove(parameter);
      cmd.CommandText = cmd.CommandText.Replace(parameter.ParameterName, newParameterSQL.ToString());
    }
}

[)amien

Avoiding SQL injection

Back in ’98 I was developing an extranet site for a local company when I realized that it would be open for exploit if somebody put single quotes in text fields. It was early in the development cycle so I fixed it and moved on, unable to find out how other people were avoiding the problem.

It turned out many were not and it became a well-known exploit called SQL injection. Unfortunately there are many developers who don’t know or appreciate the problem, and it is this:

If you build SQL by appending strings and data without correct encoding your application can be exploited. These exploits can range from exposing sensitive information, through to modification and deletion of data.

This problem is very real and applies to:

  • All SQL statements, not just SELECT
  • All database systems, not just MS SQL or MySQL
  • All programming environments, not just C#, PHP or ASP
  • All data, most essentially that obtained from end-users, regardless of client-side checking

Let’s walk through an example and see how it works and what can be done to avoid it.

Example: User login

We have a user-name and password from a web form and want to get the users ID from the database, or nothing if it wasn’t valid. We want to send something like this SQL statement to our database.

SELECT UserID FROM Users WHERE UserName='Bob' AND Password='test'

And so a developer might do something like this (in C# using .NET);

var dr = connection.Execute("SELECT UserID FROM Users WHERE UserName='" + Request("UserName") + "' AND Password='" + Request("Password") + "'");
if (dr.Read()) userId = dr.GetInt32(dr.GetOrdinal("UserID"));

The problem here is that if there is a ‘ in the form fields it effectively breaks out of the selection criteria and allows the end user to add extra criteria or even commands to what we are sending to the database server. Should they enters the following into the password form field…

    ' OR ''='

Then our code above will send the following SQL to the database:

SELECT UserID FROM Users WHERE UserName='aaa' AND Password='' OR ''=''

Which will return every record in the database and our code will let him log in as the first user it finds – normally a developer or administrator account. Ouch!

Bad solution: Encode it yourself

One solution often adopted is to always ensure all string input has a single-quote replaced by two single-quotes, which is what SQL server expects if you really want to send it a single quote.

This solution fails in that it doesn’t handle numbers or dates and falls apart in that both numbers and dates are often regionally formatted.

Good solution: Let the DB client encode it

A much better solution is to use your environment to perform all the proper encoding for you. As well as protecting you from such exploits you’ll also avoid localization problems where the string representation of something on your client is interpreted differently in your database. This can be a real problem in the UK where dates formatted by a UK web-server are sent to a misconfigured SQL server expecting US formatting and the days and months become transposed without error.

var cmd = new SqlCommand("SELECT UserID FROM Users WHERE UserName=@UserName AND Password=@Password");
cmd.Parameters.Add(new SqlParameter("@UserName", System.Data.SqlDbType.NVarChar, 255, Request("UserName")))
cmd.Parameters.Add(new SqlParameter("@Password", System.Data.SqlDbType.NVarChar, 255, Request("Password")))
dr = cmd.ExecuteReader();
if (dr.Read()) userId = dr.GetInt32(dr.GetOrdinal("UserID"));

Okay there is no doubt this is a little longer but it takes care of all our encoding and localization worries, and if for example you want to insert a lot of data into the database, creating the command and parameters once, then just setting the parameters for each insert (and executing it) will run faster than lots of string building inserts…

Moral of the story

Always encode data properly. If you can use the provided methods and functions to do so. If none are provided grab the specification and find out all the special characters used. Learn what encoding and escape sequences are used and apply them properly.

A few places where data should be encoded properly:

  • HTML – Obviously < and > have special meanings and need to be escaped. ASP.NET controls will take care of this if you set the .Text or .InnerText properties but set .InnerHTML at your own peril. Old ASP has the Server.HTMLEncode() function.
  • URL – A whole host of rules but the query string is often modified in code. Use URLEncode() or something similar especially if you want XHTML compliance too.
  • XML – Again a whole host of rules for what is valid data. Either use an XML object to write out your data (MSXML, Xerces etc) or maybe even store it in [[CDATA sections.
  • CSV – Even comma-separated value files have encoding rules. What do you need to do if text is going to have a ” in a field. What happens if a number contains a comma! Find out or use a well regarded library to do it for you.

Notes about the example

A better login system would not allow the web server direct access to sensitive data such as the user table. All access to sensitive information should be through stored procs that enforce those restrictions.

Such a login system would therefore call a stored procedure that logged the attempted, decided if it was valid, and locked out the user if too many incorrect attempts. I’ll blog that if anyone is interested.

Even if you don’t want to do that, returning a single field is better achieved by using ExecuteScalar() and forgetting a data reader.

Microsoft have a developer how-to on injection.

[)amien