Insert Record issue

[cbarnhart]

I have a form created in LiveCode. The user can add web blogs they find interesting and this info is loaded into a server based mySQL table. If the user types the info into the fields all is well, but if they cut from a internet site and paste into the field, the information will not load into the tables. At first I assumed it was a formatting issue brought over from the web page, so I added a paste routine to remove any formatting-

Code: Select all

on rawKeyDown pKey
   if the environment <> "development" then pass rawKeyDown
   if pKey = 118 then
      if the commandKey is down or the controlKey is down then
         put the clipboardData["text"] into tText
         if tText <> "" then 
            if the selectedChunk <> "" then
               put tText into the selectedChunk
            end if
         end if
      else
         pass rawKeyDown
      end if
   else
      pass rawKeyDown
   end if
 
end rawKeyDown

on pasteKey
   put the clipBoardData["text"] into tText
   if tText <> "" then
      put the selectedChunk into tSelected
      if tSelected <> "" then
         put tText into the selectedChunk
      end if
   else
      pass pasteKey
   end if
end pasteKey

But I am still having the issue. Any suggestions?

[Simon]

Hi cbarnhart,
I just pasted "http://forums.runrev.com/phpBB2/viewtop ... 12&t=16762" into a field of my DataGrid Form and it updated successfully to mySQL. Didn't do anything special with pasteKey.

Could you post an example url?

Simon
EDIT: ooops Insert... looking
EDIT: Insert worked as well

[bangkok]

But I am still having the issue. Any suggestions?

Yes.

More than the formatting, you have to get rid of :
-tabulations
-CR and/or LF
-single quotes

etc.

ALl those special characters can have effets on the MySQL DB.

So here a few lines to clean up the string before the insert or the update :

Code: Select all

  replace tab with empty in myData
   replace quote with empty in myData
   replace numtochar(11) with empty  in myData
   replace numtochar(10) with "" empty  in myData

If you need to keep single quote in your string and/or CR then :

Code: Select all

   replace "'" with "\'" in myData
   replace CR with "\n" in myData
  replace numtochar(13) with "\n" in myData

[cbarnhart]

Thank you, thank you, thank you -
Bangkok - your clean-up code did the trick

[phaworth]

You don;t need to do any data cleanup if you use the variable substitution form of the various revDatabasexxx commands. The dictionary explains what that is. Using that mehtod also protects against SQL injection attacks.
Pete