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
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.
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.
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. 
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
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.
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.
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.
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.
Didn’t work. Bad SQL.
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 
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
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'\\'''