UPDATE table and quote in a text

[Horst]

Hey Trevor,

This works (of course) fine :-)

Thanks a lot also to Mark and Viktoras

Horst


Trevor DeVore wrote:
> 
> 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_______________________________________________
> use-revolution mailing list
> use-revolution at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-revolution
> 
> 

-- 
View this message in context: http://www.nabble.com/UPDATE-table-and-quote-in-a-text-tp15529083p15560555.html
Sent from the Revolution - User mailing list archive at Nabble.com.