Inserting string with special characters into MySQL field

[ghettocottage]

is there an encoder to use in Livecode when I want to insert a string with " ? ; > and other such special characters? I think in PHP you would use mysql_escape_string

[ghettocottage]

after playing around with this, I have narrowed my problem down.

My SQL looks like this:

Code: Select all

   put "INSERT INTO notes (note) VALUES ('" & tNote & "');"  into tSQL
      revExecuteSQL gConnectionID, tSQL

if you look closely, I have single-quotes just inside each of the parenthesis ( )

if I have a note that has a single-quote in it, then that breaks my SQL and things come to a stop. If, however, I escape any single-quotes in my note like this: /' then things will work.

Is there a different way to write that SQL statement that would not use single-quotes?
Or, do I need to write a function that replaces single-quotes with /'
Or, is there something that already exists in livecode that will do that, like singlquoteEncoder(tNote)

I am hoping for the latter, but will try the former for now.

[ghettocottage]

I seem to have found a "sort of" solution:

Rather than use single quotes, my SQL statement looks like this:

Code: Select all

   put "INSERT INTO notes (note) VALUES (" & quote & tNote & quote &");"  into tSQL

this allows me to have single quotes in my note, but not double quotes.

So, using some other functions I found, I wrote this function:

Code: Select all

function quoteEncode pString
   put pString into tEncoded
   replace quote with "\" & quote in tEncoded
   return tEncoded
end quoteEncode

and use that to escape the quotes in the string, like:

Code: Select all

   put fld "note" into tNote
   put quoteEncode(tNote) into tNote

works, and now I can make notes on the code I am writing and store that in my database.

Is there a better way to do this?

[quailcreek]

Hi ghettocottage,
I'm hooping you've gotten a final solution to this problem. I'm trying to do the exact same this you are. That is, being able to have a single quote in the input control. I've tried all of what you documented here and still no-joy. Right now it allows me to have any other special chars, including double quotes. Probably because I have the column defined as TEXT in the Db. Any help would be great.

[ghettocottage]

I have not played with it in a while, but here are my little functions that worked for me:

Code: Select all

--this function escapse carriage returns out of strings from a database. to go into a datagrid
function crEncode pString
   put pString into tEncoded
   replace CR with "...." in tEncoded
   return tEncoded
end crEncode

function crDecode pString
   put pString into tEncoded
   replace "/cr" with CR in tEncoded
   return tEncoded
end crDecode

--this function escapes quotes out of strings to go to database.
function quoteEncode pString
   put pString into tEncoded
   replace quote with "\" & quote in tEncoded
   replace CR with "\n" in tEncoded

   return tEncoded
end quoteEncode

putting a note into my database:

Code: Select all

  --get your note and id
   put fld "note" into tNote
   put quoteEncode(tNote) into tNote
   put fld "note_id" into tID
   
      if tID is not empty
         then
   
   put "UPDATE notes SET note= "  & quote & tNote & quote & " WHERE id = " & tID &  ";"  into tSQL

and getting a note like:

Code: Select all

put tSelected[note]   into tNote
    --decode the carriage return
    put crDecode(tNote) into fld "note"

probably not the best of ways, but it was working for me

[quailcreek]

What environment are you on? Maybe SQLite reacts differently on iOS.
If pTheName is something Fred's with a ' it throws a near "s": syntax error

Code: Select all

 put "INSERT INTO MyInfo (theName) VALUES (" & quote & pTheName & quote & ");"  into tSQLStatement

Any thoughts?

[ghettocottage]

I am on Linux on my Desktop, and my server is also Linux. I am using Livecode 7.4 community.

We should be having similar outputs if you are on a mac ( guessing)

What does your tSQLStatement look like after it is put together? Mine looks like this if I put it in a field rather than sending it:

Code: Select all

INSERT INTO notes (note) VALUES (" Fred's with a 'single'  quote and \"double\" quotes");

[quailcreek]

I think that my make a difference. I am developing on a MAC but for an iPhone app. Testing on the symm and device yield the same errors. I don't think the back slashes will work. I've tried every combination of singe quote and double quote, and & quote & I can think of. I haven't tried the back slashes yet.

Code: Select all

INSERT into MyInfo(theName) VALUES ("Fred's");

[quailcreek]

I caught my error. Your code works just fine. I should have looked at he error more closely. It wasn't the single quote in the ptheName variable it was from further down in my code.

Thanks ghettocottage

[dave.kilroy]

Hi - I'm managing to use single quote marks (what I call inverted commas) in my SQL statement and also have inverted commas and quotation marks in the data to be inserted/updated into my mySQL database...

First I simply put the htmltext of my main content field into an array

Code: Select all

      put the htmltext of fld "fldMainContent" into tData["content"]

Then I make allowances for some special chars and after URLencoding, manage inverted commas (%27) by doubling them up

Code: Select all

   repeat for each key tKey in pData
      put pData[tKey] into tString
      replace "%" with "%" in tString
      replace "+" with "+" in tString
      replace "\" with "\" in tString
      put URLEncode(tString) into tString
      replace "%27" with "%27%27" in tString
      put "&" & tKey & "=" & tString after tPostData
   end repeat

Then I send data up to my .lc script, and after URLdecoding I update the mySQL database

Code: Select all

   put "UPDATE apps SET content='" & tcontent & "'," && "secs='" & tsecs & "'" && "WHERE appID='" & tappID & "'" into tSQL
   revExecuteSQL tConID,tSQL

Doubling up the inverted commas this way (which I got from Ian Macphail - and possibly also Neil Rogers?) works ok for me with htmlText but I haven't tried it with with unicode - have any of you?

Kind regards

Dave

EDIT: meant to say, doubling up this way results in a single inverted comma being inserted/updated into mySQL so there is no need to manage anything when getting the same data back out of the db...

[quailcreek]

Thanks, Dave. There are some bits and pieces I can use.

[Adrien]

Hello,

You want to prepare your SQL statements. This is an exemple:

Code: Select all

      put "INSERT OR REPLACE INTO awesome_table (id_guy, id_thing, status) VALUES (:1, :2, :3)" into SQLiteQry 
      put the guyID of this stack into SQLArray[1]
      put tThingID into SQLArray[2]
      put "Unkown, maybe?" into SQLArray[3]
      revExecuteSQL the dbID of this stack, SQLiteQry, "SQLArray"

Anything goes into SQLArray!

And later in your code, you can also change SQLArray[2] for instance and re-run the query using the same revExecuteSQL line (nice if you have a loop or something).


Cheers,

[quailcreek]

Thanks, Adrien,
I always learn a lot from your examples of SQL statements. I like the INSERT OR REPLACE. That way you only need one statement to cover both instances. I've been using an INSERT statement and another UPDATE statement. Yours is more concise.

[dave.kilroy]

Yes thanks Adrien, I hadn't hear of "INSERT OR REPLACE" and will give it a try!

[Adrien]

Hi,

NOTE!! INSERT OR REPLACE is for SQLite, I read a bit fast, my bad.
For MySQL, I think you want to use: INSERT .... ON DUPLICATE KEY UPDATE ....
There is also the REPLACE INTO command in MySQL but, there's a big thing: it will delete then insert. This means, if you have an auto increment field, the external references to that value should break if you're not careful with it (need "on update, cascade"? I'm not sure...).

For SQLite as well as MySQL too I believe, something great to use with INSERT OR REPLACE is the COALESCE.

COALESCE has at least 2 arguments and returns the first non-null of them, or if all null, it returns NULL.

This way, you can INSERT OR REPLACE while keeping old values!
In this exemple, let's say idGuy is the primary key of table best_table. Well actually that doesn't make a lot of sense but that's for the sake of the discussion.

Code: Select all

      put "INSERT OR REPLACE INTO best_table (idGuy, idHouse, spergle, thing) VALUES " into SQLiteQry
      put "(:1, :2', COALESCE((SELECT spergle FROM best_table WHERE idGuy = :1 LIMIT 1), NULL), :3)" after SQLiteQry -- note the reuse of :1

COALESCE here has the minimal 2 arguments: if a "spergle" is already set for the record identified by the primary key idGuy, the COALESCE will return it, otherwise it will return NULL. There, the INSERT OR REPLACE statement will either use the previous value of "spergle" for our row, or NULL. Note, this 2nd argument, NULL, could have been another SELECT, or a fixed value like 2 or "N/A", or anything else, almost. Just remember, if every argument has a result of null, the value ultimately returned by COALESCE will be NULL.

But there I'm drifting away from the topic's question

Once more, sorry for the confusion with SQLite and MySQL on that INSERT OR REPLACE /// INSERT .. ON DUPLICATE KEY UPDATE / REPLACE INTO

Cheers!