Escaping Strings with Single Quotes

[mphasize]

Hi,

I'm using this php client and I want to insert some new data records into my CartoDB table which contain single-quotes. I have seen, that the classes don't provide any solutions for this yet, so I'm trying to work out my own escaping solution.

As discussed here, a simple Backspace-Escape should work for single quotes (and from what I know this usually works fine):
http://support.cartodb.com/discussions/problems/50-problem-with-apostrophe

But something seems to get messed up anyway and I just get a syntax error.

Here's the query I'm passing to the runSql Function:

INSERT INTO handel_v2 (the_geom,crm_id,firma) VALUES(GEOMETRYFROMTEXT('POINT(3.7941725 41.9231994)', 4326),'internal ID','Name with' single-quote');
SELECT handel_v2.cartodb_id as id, handel_v2.* FROM handel_v2 WHERE cartodb_id = currval('public.handel_v2_cartodb_id_seq');

Any Ideas? (Anything else I can provide to track this down?)
Thanks!
M

[mphasize]

Ok, I just found a similar (and already fixed) issue in the Ruby client for CartoDB and the solution seems to be to escape single quotes with another single quote.

function escape_query($str) {
    return strtr($str, array(
        "'"  => "''"
        // which other characters need to be escaped?
    ));
}

It would be nice, when the client classes could handle this automatically, at least in function like insertRow()

Best!
M

[jatorre]

The library I think is quite outdated now on probably is missing lot of these things.

Actually if you want to propose pull requests that would be awesome!

On Sep 6, 2012, at 8:45 AM, Marcus wrote:

Ok, I just found a similar (and already fixed) issue in the Ruby client for CartoDB and the solution seems to be to escape single quotes with another single quote.

function escape_query($str) {
return strtr($str, array(
"'" => "''"
// which other characters need to be escaped?
));
}
—
Reply to this email directly or view it on GitHub.