carto - Avoiding SQL injection when updating tables through CartoDB API

I'm trying to write a routine which synchronises data in our database with the CartoDB database, so we can display it on a map. It's simple things like names and contact details, which our users have the ability to change online. It's unlikely to be a problem but nevertheless it's prudent to worry about SQL injection.

Is there a recommended way to do this through the SQL API? I'm writing queries such as:

https://ouraccount.cartodb.com/api/v2/sql?q=update our_table set surname = 'Smith' where cartodb_id = 5&api_key=123456789

I'd rather not try to sanitize the 'Smith' part myself, and I can't find any documentation saying that the API supports parameterisation. What do people do here?

Answer

EDIT: My old answer only pertained to the regular CartoDB API, which allows read access only.

You didn't specify in your question but it seems like you are trying to have the client push the update request directly to the API. This is not a good idea. Once your API key is released to the public anyone can make requests using it. It should remain on your server. Clients can interact with it using OAuth in combination with your key. So, to summarize:

Do NOT generate write SQL (INSERT, UPDATE, DELETE) on the client side. Anything that requires an API key should be done on your server through OAuth interactivity. Anything read access without needing a key is fine.

The purpose of the SQL API with key is to allow your server application, not the client, to run update queries on CartoDB. It has some basic checks to validate whether or not you are passing good SQL, but it won't parameterize your queries for you.

I'd rather not try to sanitize the 'Smith' part myself

Unfortunately you will need to. The part of the SQL API that updates tables currently looks for some basic invalid queries but isn't designed for the client to directly interact with it. What you need to do is collect the query parameters from your user, escape accordingly, and let your app interface with the API itself.

Is there a recommended way to do this through the SQL API? I'm writing queries such as:

In this link CartoDB outlines an as-yet-to-be-developed method for building "Named Queries" to interact with its API. Although its usage has not been implemented within the API at the time of this post it provides good insight as to how you might build up a parameterized write query on your server:

Query templates would reference parameters using the <%= and %> separators. There would be system parameters (provided by the server, like client IP or similar) and user parameters (provided by the user at invocation time).

In order to avoid SQL injection, each parameter will be of a given "type", which determines the required escaping for it. Supported types are:

sql_literal (internal single-quotes will be sql-escaped) sql_ident (internal double-quotes will be sql-escaped) number (can only contain numerical representation) ... (add more as need arises) Valid user parameter names start with a letter and can only contain letters, numbers or underscores. System parameter names are prefixed with sys:: to avoid clash with user parameters.

User parameters need to be explicitly defined at NamedQuery creation time, with their defaults (for checking SQL validity).

// namedquery.json 

{
  version: '0.0.1',
  // there can be at most 1 template with the same name for any user 
  // valid names start with a letter and only contains letter, numbers
  // or underscores
  name: 'query_name', 
  // embedded authorization certificate
  auth: {
   // See https://github.com/CartoDB/Windshaft-cartodb/wiki/Signed-maps
   method: 'token', // or "open"

   valid_tokens: ['auth_token1','auth_token2'] // only for 'token' method
  },
  // Variables not listed here are not substituted
  // Variable not provided at instantiation time trigger an error
  // A default is required for optional variables
  // Type specification is used for quoting, to avoid injections
  placeholders: {
      x: { type:'number', default:0 },
      y: { type:'number', default:0 },
      z: { type:'number', default:0 },

      iso2: { type:'sql_literal', default: 'ES' }
  },
  sql: "select CDB_TorqueTile(" +
       "$$ SELECT * FROM countries WHERE iso2 = '<%= iso2 %>' $$," +
       "<%= z %>, <%= x %>, <%= y %>)",
  affected_tables: 'countries' // optional
}

For further reading, also see this question.