MySQL INSERT in AppleScript

Hi guys! After a good bit of searching I’m almost done with the AppleScript I’m trying to create.

However, one issue is rearing its head - a MySQL insert isn’t functioning correctly.

This is the statement:

do shell script "/usr/local/mysql/bin/mysql -D " & mysql_db & " -u " & mysql_user & " -h " & mysql_host & " -p" & mysql_pw & " -e  " & "\"INSERT INTO " & mysql_db & "." & mysql_table & " (qid,unitNumber,jobName,jobClient,quotaGB,dateCreated) VALUES (NULL," & jobNum & "," & jobName & "," & jobClient & "," & jobSize & ",CURRENT_TIMESTAMP)\";"

I’ve added the ` in to see if that makes a difference, tried it both with and without. I’m missing something simple I’m sure, and can’t figure out what!

This is the error I’m getting:

…basically it seems to dislike any form of input for jobNum, jobName, jobClient, and jobSize. I’ve solved that before with the use of a `, but it’s not appreciated here it seems.

Any help would be much appreciated.

This isn’t an answer and I haven’t really looked at it yet, but I think that MySql uses the single hyphen around everything to escape stuff with.

You must look that up, and if that is so, then you might possibly use either the quoted form of command, or create your own escaping by surrounding your variables with “double single double” hyphen sequences.

This should at least be valid for your valid for your value sections, if I’m not totally wrong then.

set searchstr to "_EATLES"
set searchstr to "'" & searchstr & "'" 

Hopes this helps and Best Regards


Heading vaguely towards a clearing - however the confusion exists because the failed command copied and pasted to the shell inserts in to the database perfectly!

I’ll continue with the escaping attempts. Thanks :slight_smile:

Couple of ideas, although I am admittedly not a MySQL person.

Are any of the fields text fields? It seems to me they should be enclosed in single quotes:

…VALUES (NULL, 1234, ‘Job name here’, ‘Job Client here’, ‘Large’, CURRENT_TIMESTAMP)

Should the final ; be inside the final quote? Seems to me that should be the end of the SQL syntax.

Like this:

…VALUES (NULL," & jobNum & “,” & jobName & “,” & jobClient & “,” & jobSize & “,CURRENT_TIMESTAMP);"”

instead of:

VALUES (NULL," & jobNum & “,” & jobName & “,” & jobClient & “,” & jobSize & “,CURRENT_TIMESTAMP)";”

Your code with my suggested changes:

do shell script "/usr/local/mysql/bin/mysql -D " & mysql_db & " -u " & mysql_user & " -h " & mysql_host & " -p" & mysql_pw & " -e  " & quote & "INSERT INTO " & mysql_db & "." & mysql_table & " (qid, unitNumber, jobName, jobClient, quotaGB, dateCreated) VALUES (NULL, '" & jobNum & "', '" & jobName & "', '" & jobClient & "', '" & jobSize & "', CURRENT_TIMESTAMP);" & quote

I’ll tell you how I handled it. Understand that I used sqlite3 and not mysql, but I assume you’re running into the same problem I encountered.

When you pass commands from applescript to the command line and then into sqlite, your strings have to pass the character escaping rules of both bash and sqlite, and account for applescript’s escaping rules. You can avoid all of the bash rules for special characters by using the “quoted form of” to place single quotes around all of your values. The only character in your strings you have to worry about with this is single quotes. In bash we escape them with the backslash. To pass an escaped single quote from applescript through to bash we have to escape the escape character “\'”. It’s messy. Next, for sqlite we only have to worry about the single quote and we escape that by using a double single quote.

So you can see the problem is that applescript, bash and sqlite escape the single quote character differently. This is the problem. I solved the problem by completely removing the single quote from any of my strings by replacing it with a dummy value. In my case I replaced single quotes with “(@]”… which I figured was a character sequence I was very unlikely to come across. So by completely removing the single quote I didn’t have to worry about the applescript, bash or sqlite rules.

This has the side-effect that every time you pull information out of the database you have to do the opposite and replace “(@]” with “'”. This also has the side effect in that if you want to perform a search of the database then your search terms must go through the same process of replacing the single quote before running your search, because that’s how your strings are entered into the database.

It’s a huge pain!!! But I couldn’t find a better solution. See if the following script works.

ALWAYS REMEMBER: you can’t surround any of your values with double quotes, you must always use single quotes to avoid problems with bash special characters. As such you’ll see that I use “quoted form of” around your entire INSERT statement as well as around each individual item. And each individual item must go through the escapeForSQLite3() handler. And when you pull values out of the database you must send them through the unescapeForSQLite3() handler in order to get the original value back.

do shell script "/usr/local/mysql/bin/mysql -D " & mysql_db & " -u " & mysql_user & " -h " & mysql_host & " -p" & mysql_pw & " -e  " & quoted form of ("INSERT INTO " & mysql_db & "." & mysql_table & " (qid, unitNumber, jobName, jobClient, quotaGB, dateCreated) VALUES (NULL, " & quoted form of escapeForSQLite3(jobNum) & ", " & quoted form of escapeForSQLite3(jobName) & ", " & quoted form of escapeForSQLite3(jobClient) & ", " & quoted form of escapeForSQLite3(jobSize) & ", CURRENT_TIMESTAMP);")

on escapeForSQLite3(theText)
	return my findReplace(theText, "'", "(@]")
end escapeForSQLite3

on unescapeForSQLite3(theText)
	return my findReplace(theText, "(@]", "'")
end unescapeForSQLite3

on findReplace(theText, findText, replaceText)
	if theText contains findText then
		set text item delimiters to findText
		set a to text items of theText
		set text item delimiters to replaceText
		set theText to a as text
		set text item delimiters to ""
	end if
	return theText
end findReplace