UPDATE table and quote in a text

[Trevor DeVore]

On Feb 17, 2008, at 5:23 PM, Horst wrote:
> I know, the easiest way would be to encode the data with  
> base64encode. But
> there must also be a way to  save a text f.e. like 1234'5'6öä'ioup"#  
> in a
> varchar or text type. With base64encode it will f.e. not be possible  
> to use
> the "fulltext" indexing, which I will need. No, there must be  
> another way.
> SQL is not that silly and I learned, that RR is a a powerfull tool,  
> if you
> (or someone else) knows the "tricks". Once again the question: How  
> to save
> any text via RR to a SQL-Table as described before.


Hi Horst,

You can definitely insert any text into a database using Rev as long  
as you cleanse the input first. I've attached a modified handler from  
libDatabase that you can use to escape strings you want to use in an  
UPDATE clause.

SQLite only requires for single quote to be escaped. MySQL has a  
slightly more complex escape sequence as does PostGreSQL. I haven't  
tested the postgresql code myself but I based the code off of some  
docs I found somewhere.

Here is an example of how you could use it:

...
put escapeStringForSQL("mysql", the text of field "UserSuppliedData")  
into theData
put format("UPDATE my_table SET user_data = '%s' WHERE ID = %u",  
theData, theID) into theSQL
....


Regards,

-- 
Trevor DeVore
Blue Mango Learning Systems
www.bluemangolearning.com    -    www.screensteps.com



function escapeStringForSQL pDBType, pString
     switch pDBType
         case "mysql"
             replace numtochar(92) with numtochar(92) & numtochar(92)  
in pString --> \ to \\
             replace numtochar(39) with numtochar(92) & numtochar(39)  
in pString --> ' to \'
             replace numtochar(34) with numtochar(92) & numtochar(34)  
in pString --> " to \"
             replace numtochar(0) with numtochar(92) & numtochar(48)  
in pString --> NULL to \0
             replace numtochar(26) with numtochar(92) & numtochar(90)  
in pString --> Control-Z to \Z
             replace numtochar(10) with numtochar(92) & numtochar(110)  
in pString --> newline to \n
             replace numtochar(13) with numtochar(92) & numtochar(114)  
in pString --> carriage return to \r
             replace numtochar(9) with numtochar(92) & numtochar(116)  
in pString --> tab to \t
             replace numtochar(8) with numtochar(92) & numtochar(98)  
in pString --> backspace to \b
             break
         case "postgresql"
             replace numtochar(92) with numtochar(92) & numtochar(92)  
in pString --> \ to \\
             replace numtochar(39) with numtochar(39) & numtochar(39)  
in pString --> ' to ''
             replace numtochar(12) with numtochar(92) & numtochar(102)  
in pString --> formfeed to \f
             replace numtochar(10) with numtochar(92) & numtochar(110)  
in pString --> newline to \n
             replace numtochar(13) with numtochar(92) & numtochar(114)  
in pString --> carriage return to \r
             replace numtochar(9) with numtochar(92) & numtochar(116)  
in pString --> tab to \t
             replace numtochar(8) with numtochar(92) & numtochar(98)  
in pString --> backspace to \b
             break
         case "sqlite"
         default
             replace numtochar(39) with numtochar(39) & numtochar(39)  
in pString --> ' to ''
             break
     end SWITCH
end lib escapeStringForSQL