The Dreaded Quotations Inside a String

[McUsrII]

Hello.

I was concerning myself more with the text field, and “large text inputs” by the “insert into” statement. I figured I’d be pretty safe if I encoded the whole text like you would encode html text, and then encode that text back again (Think enocoding AppleScripts or url’s for that matter). Then I thought, as long as I don’t change encoding on my machine between runs, then I’d be pretty safe, and independent of what encoding the mysql database uses on its side of things too.

Edit

Just to clear up something.

@me

And a way to get things right, if the MySql connection is over a socket to somewhere else. Anyways, one needs to read the manual carefully in order to get things right.

I didn’t mean that mysql didn’t use a socket when mysql was local, I meant when the socket pointed to somewhere else, maybe that should have been clearer written, hindsightly. I am no expert on sockets (though I have used them on at least one C-project), but you can’t escape them when setting up mysql connections.

[McUsrII]

@vince

Haven’t tried this, but I think it might be easier that several levels of escapes which can only be tested by trial and error. Can you just replace single and double quotes with the curly versions? I don’t think shell recognizes that as a regular quote…

Hello vince.

Now, that is a scheme that may work perfectly well, as long as you can guarantee, that there will be no smart quotes or back ticks in the text you are encoding.

[daBee]

Unfortunately, I won’t go near PHP (yech) and Ruby doesn’t have any hooks to grab current tab title and current tab url of Safari. I would have gone to Ruby well before AppleScript.

In any case, sorry for the delay in posting. I just got back to this and have eradicated quotations altogether.

@DJ Bazzie Wazzie

@McUsrII

I feel a little bit sorry for you, because it can be complex to figure out something that works, since it has to work on both the AppleScript side, and on the MySql side of things.

MySQL escaping depends on the character set of the connection, you’re correct that it more depends than shell and AppleScript alone. This is one of the many reasons I wrote my own MySQL osax and haven’t used the mysql command for more than a decade. If the TS insist to use a shell I would consider to use Ruby or PHP who has built in MySQL libraries with proper MySQL escape functions.

[daBee]

@DJ Bazzie Wazzie

But to answer DaBee's question here is some old code:

Didn’t work. Bad SQL.

[DJ_Bazzie_Wazzie]

Since you haven’t post the query you want to escape I have posted an non-working dummy query to show how to use variables in a query. Obviously it won’t work on your machine

[daBee]

The script creates the SQL just fine:

"SELECT * FROM `urls` WHERE `title` = 'John\\'s bike' AND `url` = 'Administrator\\'s file'"

That is invalid SQL. That is your very example.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's bike' AND `url` = 'Administrator\\'s file'' at line 1

[DJ_Bazzie_Wazzie]

@daBee

That is your very example.

I have no idea what’s going wrong on your machine but when I add the option -D (to get rid of no-database-selected error) and select any database, I get the error table “table” does not exist, the query itself is just fine. Also the query created by my script doesn’t look as you describes but looks like:

'SELECT * FROM `table` WHERE `field1` = '\\''John\\'\\''s bike'\\'' AND `field2` = '\\''Administrator\\'\\''s file'\\'''