2

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?

2

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.

  • Thanks for that @Conor; I didn't realise that was available. I can't find anything about parameterisation, though. As far as I can tell the only input we can pass to the query is with the q attribute. For example, if Mr Smith wants to be nasty he could change his name to: Smith';update our_table set surname = 'Jones' where cartodb_id=1; update our_table set surname = 'Smith – Michael Clark Feb 12 '15 at 15:35
  • SQL API is created to use from both client side and server side. Also there is no named queries support right now, where did you see that? – javisantana Feb 13 '15 at 8:31
  • @javisantana - I assume the idea is that we implement our own. Not ideal, but at least I now know what I should be doing. Thanks for your help Conor. – Michael Clark Feb 13 '15 at 10:07
  • @javisantana Could you clarify what you mean? Your comments on the question I linked suggest that you believe that you should not make calls from the client side: " If you do that from client side you will expose the api_key to everyone and that's something you don't want to do". Seems to contradict your comments now. The "named queries" link is just an example structure that shows how you could build up a parameterized query on your server to send as a SQL API request to CartoDB. – Conor Feb 13 '15 at 15:01
  • 2
    @Conor first of all, I'd change "DO NOT GENERATE SQL ON THE CLIENT SIDE... EVER" by "do not use api_key on client side... ever". In CartoDB you not only should generate SQL client side, you are encouraged to do it because is one of the powerful things you can do with it. If you are going to generate SQL queries server side you need to take exactly the same care you'd do using any sql database. – javisantana Feb 16 '15 at 8:12
0

There might be a better resource than this CartoDB blog post, but you can now create sql/plpgsql functions that have very limited uses that you can then expose publicly, e.g.:

CREATE OR REPLACE FUNCTION name_change (
    _surname TEXT,
    _id INT)

RETURNS integer
AS $$
BEGIN
   EXECUTE 'UPDATE ourtable
           SET surname = $1
           WHERE cartodb_id = $2
           ' USING (_surname, _id);
   RETURN 1;
END;
$$
LANGUAGE plpgsql SECURITY DEFINER ;

--Grant access to the public user
GRANT EXECUTE ON FUNCTION name_change( text, int) TO publicuser; 

And then access that with something like:

https://ouraccount.cartodb.com/api/v2/sql?q=SELECT name_change('Smith',5)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.