The Dreaded Quotations Inside a String

Hi folks.

Just trying to get my head around escaping inside a string. I can’t find a way to escape them. Anybody have a quick handler that manages this?

Cheers

Hey daBee,

Escape what? In what context?

A string in AppleScript? A string to be run in a do shell script command?

More information please.

Hello.

Maypbe post #8 in MacScripter / escaping backslash? may help you.

It escapes a string correctly like you would have to, to use it in an osascript or a do shell script.

(It takes whatever text that is on the clipboard and escapes quotes and backslases and pastes the result back to the clipboard if you are happy with the escaping, when you have seen the result).

I have a string that’s being captured somewhere else. Single quotations (and possibly double quotations) are showing up, which is messing up a shell script that’s shoving that string into mysql.

For instance, it’s tripping up on these:

replace("\"", "\\\"", myT)
replace("'", "`", myT)

Just trying to escape for shell script into mysql.

Cheers

Edited: Sorry, forgot the handler I am trying to use now:

on replace(A, B, theText)
    set L to length of A
    set K to L - 1
    set P to offset of A in theText
    repeat until P = 0
        if P = 1 then
            set theText to B & text (L + 1) through -1 of theText
        else if P = (length of theText) - K then
            set theText to text 1 through -(L + 1) of theText & B
        else
            set theText to text 1 through (P - 1) of theText & B & text (P + L) through -1 of theText
        end if
        set P to offset of A in theText
    end repeat
    return theText
end replace

Hello.

See if this works, here stuff are escaped twice, there may be that mysql requires other quoting than shell scripts, and escaping of osa scripts within do shell scripts in AppleScript, and there I can’t really help you.

(I used my script mentioned above, and escaped it for inserting into an osascript within a do shell script.)

MySQL takes single escapes, but I can’t get AS to recognize these escapes before I get out into the shell even. Your example has completely stumped me as to what it’s trying to do. Can you elaborate?

Cheers

It insulates double escaping, so that the escaped quotes will be passed to the receipient as escaped quotes. It does so, by inserting literal escapes in front of the escape character. (\ is a literal escape, and is inserted in front of " , which makes \", which again is passed further down the stream as ").

set res to do shell script "echo \"replace(\\\"'\\\", \\\"\\`\\\", myT)\""
”> "replace(\"'\", \"`\", myT)"

But in order for this to work, I had to insert a literal escape before the backtick. (\) Which I had to, because the shell barked at a single backtick.

OK so now it’s catching on the single quotation:

replace(“'”, “\'”, myT)

Let me expand that here: replace( " ’ ", " \ \ \ ’ ", myT)

SE is balking on the last ’ as the replacement. I’d be happy to use a backtick, but it isn’t working either.

This is more like REGEX than anything else.

Cheers

Hello, I did a little edting of your expression into this, that could be taken as a regular escaped string in AppleScript:

"replace( \" ' \", \"' \", myT)"

Then I escaped that into:

"\"replace( \\\" ' \\\", \\\"' \\\", myT)\""

(I used my script, and used the "escape for do shell script button).

If I had started with your string as a pure Applescript expression:

replace("'", "\\'", myT)

I would have ended up with this:

"replace(\"'\", \"\\\\'\", myT)"

using the do shell script button.

And:

"\"replace(\\\"'\\\", \\\"\\\\\\\\'\\\", myT)\""

This when double escaping by the “osa script button”

I hope one of those versions work for you.

Heh not one. What editor are you using? I’m in the Script Editor 2.7.

Hello.

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.

I dug up this thread for you, thinking it might help: MacScripter / MySQL INSERT in AppleScript

:slight_smile:

The SQL is later on. It’s not the bother yet. So here’s where I am now:

set myT to “Her Sister’s Friend”
set myT to replace(“'”, “`”, myT)

on replace(A, B, theText)
set L to length of A
set K to L - 1
set P to offset of A in theText
repeat until P = 0
if P = 1 then
set theText to B & text (L + 1) through -1 of theText
else if P = (length of theText) - K then
set theText to text 1 through -(L + 1) of theText & B
else
set theText to text 1 through (P - 1) of theText & B & text (P + L) through -1 of theText
end if
set P to offset of A in theText
end repeat
return theText
end replace

As you can see, it’s a back-tick. This seems to be working, before the MySQL challenge.

Hello.

If I assume that mysql handles text when the text is passed between singlequotes well, then you could really just use

quoted form of whatever

However, you would have to escape any singlequotes within the string first, and this means that you should replace apostrophe with backslash-backslash-apostrophe. Before using quoted form of the resulting string.

I find text item delimiters to be best when doing such escapes.

set mstring to "Nick's records"

set fixed_string to quoted form of (prepMysql for mstring)
log fixed_string
--> "'Nick\'\''s records'"
on prepMysql for aString
	set {tids, AppleScript's text item delimiters} to {AppleScript's text item delimiters, "'"}
	set bits to text items of aString
	set AppleScript's text item delimiters to "\\'"
	set aString to bits as text
	set AppleScript's text item delimiters to tids
	return aString
end prepMysql

(The strings must of course, first be escaped, so they can be used by appleScript as strings, then
this “post processing” ensures that it is “edible” by Mysql, if it is so, that it can take strings that are quoted.)

Edit
I see what you have posted, and from what I can see, you have replaced apostrophes with back ticks, which makes my solution superfluos, (before the quoted form).

Hello.

I just come to think about it, if there is any backquotes in any text you insert into mysql, which have their apostrophes replaced with backquotes, then you are in peril, when you transform the text back.

I think, that at least in theory my solution of escaping apostrophes before inserting the text using quoted form of are indeed better. But, then again, I don’t know what you know about the text you are going to insert, if you know for sure, that back-ticks will never occur, then your way of doing it is as good as any other. :slight_smile:

Did you tried the good old :

set myT to "Her Sister's Friend"
quoted form of myT

Yvan KOENIG (VALLAURIS, France) dimanche 22 mars 2015 16:01:26

I didn’t then, but I thought of it later, and tried it now, it is better and simpler than my way of doing things. Though the end result should be the same, still, just using quoted string of, is the more correct way to do things. :slight_smile:

Thanks Yvan.

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.

If MySql resides on the local machine, then there should be some roome, for getting things right, when choosing encoding and so on then, provided one reads the manual. 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.

Encoding problems seems to be here to stay. :confused:

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…

Local or not doesn’t really matter, the connection is always through an socket (tcp or unix domain) and never directly. You’re pretty safe when database and connection are both utf-8 (use --default-character-set option) because do shell script is encoded the same. Still escaping done by the MySQL API considering the character encoding for the connection between client and server is the best solution. For using arbitrary data in queries and you still want using a shell script I still recommend to use PHP or Ruby.

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

set preparedQuery to "SELECT * FROM `table` WHERE `field1` = '{_1_}' AND `field2` = '{_2_}'"
set query to createQuery(preparedQuery, {"John's bike", "Administrator's file"})

do shell script "/usr/local/mysql/bin/mysql -u root <<< " & quoted form of query

on createQuery(query, values)
	set oldTIDs to AppleScript's text item delimiters
	repeat with x from 1 to count values
		set query to replaceSubString(query, "{_" & x & "_}", escapeMySQLValue(item x of values))
	end repeat
	return query
end createQuery

on escapeMySQLValue(val)
	set val to replaceSubString(val, "'", "\\'")
	-- next: command line can't handle null characters, they are allowed in QuarkXPress and AppleScript.
	-- to insert binaries use LOAD_FILE()
	set val to replaceSubString(val, character id 0, "")
	return val
end escapeMySQLValue

on replaceSubString(haystack, needle, replace)
	set oldTIDs to AppleScript's text item delimiters
	set AppleScript's text item delimiters to needle
	set ti to text items of haystack
	set AppleScript's text item delimiters to replace
	set s to ti as string
	set AppleScript's text item delimiters to oldTIDs
	return s
end replaceSubString