Re: UPDATE table and quote in a text
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@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. ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: UPDATE table and quote in a text
in sqlite ' should be escaped with '' (e.g. ' and ') that's it... Viktoras Horst wrote: Holá Mark, ik wil u danken. 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. best regards Horst Mark Schonewille-3 wrote: Hallo Horst, You could write a function to escape all special characters, but the easiest and most reliable way is to encode the data with base64encode () before storing it. Best regards, Mark Schonewille -- Economy-x-Talk Consulting and Software Engineering http://economy-x-talk.com http://www.salery.biz Convert colours between different colour spaces with Color Converter. Download at http://economy-x-talk.com/cc.html Op 17-feb-2008, om 17:09 heeft Horst het volgende geschreven: Holá Mark, The main question seems to be: How to include a textfile for example: .. put 1234'5'6öä'ioup# into mytext ## I know, this text is senseless, but never mind, it should be able to be saved replace quote with \ quote in mytext ## and then, how to replave the ' ??? put UPDATE table set textfile = ' mytext '... into sql_messqage revexecutesql (my_ID), sql_message put the result ## shows an SQL-Error message and mytext will not be saved .. best regards and, as usual, a big thank You for answering Horst ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: UPDATE table and quote in a text
Dear Horst, Single quotes should be escaped the way you do and this should work fine. I have seen reports of commands starting with revdb being unable to escape single quotes, but this can be solved by using revExecuteSQL -- I don't know whether there really is a bug with escaping characters, I didn't encounter this problem myself. Make sure that you only replace single quotes that are part of the data and not the two single quotes that surround the string. Best regards, Mark Schonewille -- Economy-x-Talk Consulting and Software Engineering http://economy-x-talk.com http://www.salery.biz Convert colours between different colour spaces with Color Converter. Download at http://economy-x-talk.com/cc.html Op 17-feb-2008, om 13:24 heeft Horst het volgende geschreven: Hi there, A question to all SQL-Masters: To update a record in a SQL-Table and to store text which includes the sign in an SQL-Syntax I learnd, that it is necessary to use replace quote with \ quote in zText Well, that works fine, but what about The ' sign? replace ' with \' in zText seems NOT to work. Thanks a lot to all of you and best regards Horst -- View this message in context: http://www.nabble.com/UPDATE-table- and-quote-in-a-text-tp15529083p15529083.html Sent from the Revolution - User mailing list archive at Nabble.com. ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
UPDATE table and quote in a text
Hi there, A question to all SQL-Masters: To update a record in a SQL-Table and to store text which includes the sign in an SQL-Syntax I learnd, that it is necessary to use replace quote with \ quote in zText Well, that works fine, but what about The ' sign? replace ' with \' in zText seems NOT to work. Thanks a lot to all of you and best regards Horst -- View this message in context: http://www.nabble.com/UPDATE-table-and-quote-in-a-text-tp15529083p15529083.html Sent from the Revolution - User mailing list archive at Nabble.com. ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: UPDATE table and quote in a text
Holá Mark, The main question seems to be: How to include a textfile for example: .. put 1234'5'6öä'ioup# into mytext ## I know, this text is senseless, but never mind, it should be able to be saved replace quote with \ quote in mytext ## and then, how to replave the ' ??? put UPDATE table set textfile = ' mytext '... into sql_messqage revexecutesql (my_ID), sql_message put the result ## shows an SQL-Error message and mytext will not be saved .. best regards and, as usual, a big thank You for answering Horst Mark Schonewille-3 wrote: Dear Horst, Single quotes should be escaped the way you do and this should work fine. I have seen reports of commands starting with revdb being unable to escape single quotes, but this can be solved by using revExecuteSQL -- I don't know whether there really is a bug with escaping characters, I didn't encounter this problem myself. Make sure that you only replace single quotes that are part of the data and not the two single quotes that surround the string. Best regards, Mark Schonewille -- Economy-x-Talk Consulting and Software Engineering http://economy-x-talk.com http://www.salery.biz Convert colours between different colour spaces with Color Converter. Download at http://economy-x-talk.com/cc.html Op 17-feb-2008, om 13:24 heeft Horst het volgende geschreven: Hi there, A question to all SQL-Masters: To update a record in a SQL-Table and to store text which includes the sign in an SQL-Syntax I learnd, that it is necessary to use replace quote with \ quote in zText Well, that works fine, but what about The ' sign? replace ' with \' in zText seems NOT to work. Thanks a lot to all of you and best regards Horst -- View this message in context: http://www.nabble.com/UPDATE-table- and-quote-in-a-text-tp15529083p15529083.html Sent from the Revolution - User mailing list archive at Nabble.com. ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution ___ use-revolution mailing list use-revolution@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-tp15529083p15530593.html Sent from the Revolution - User mailing list archive at Nabble.com. ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: UPDATE table and quote in a text
Hallo Horst, You could write a function to escape all special characters, but the easiest and most reliable way is to encode the data with base64encode () before storing it. Best regards, Mark Schonewille -- Economy-x-Talk Consulting and Software Engineering http://economy-x-talk.com http://www.salery.biz Convert colours between different colour spaces with Color Converter. Download at http://economy-x-talk.com/cc.html Op 17-feb-2008, om 17:09 heeft Horst het volgende geschreven: Holá Mark, The main question seems to be: How to include a textfile for example: .. put 1234'5'6öä'ioup# into mytext ## I know, this text is senseless, but never mind, it should be able to be saved replace quote with \ quote in mytext ## and then, how to replave the ' ??? put UPDATE table set textfile = ' mytext '... into sql_messqage revexecutesql (my_ID), sql_message put the result ## shows an SQL-Error message and mytext will not be saved .. best regards and, as usual, a big thank You for answering Horst ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: UPDATE table and quote in a text
Holá Mark, ik wil u danken. 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. best regards Horst Mark Schonewille-3 wrote: Hallo Horst, You could write a function to escape all special characters, but the easiest and most reliable way is to encode the data with base64encode () before storing it. Best regards, Mark Schonewille -- Economy-x-Talk Consulting and Software Engineering http://economy-x-talk.com http://www.salery.biz Convert colours between different colour spaces with Color Converter. Download at http://economy-x-talk.com/cc.html Op 17-feb-2008, om 17:09 heeft Horst het volgende geschreven: Holá Mark, The main question seems to be: How to include a textfile for example: .. put 1234'5'6öä'ioup# into mytext ## I know, this text is senseless, but never mind, it should be able to be saved replace quote with \ quote in mytext ## and then, how to replave the ' ??? put UPDATE table set textfile = ' mytext '... into sql_messqage revexecutesql (my_ID), sql_message put the result ## shows an SQL-Error message and mytext will not be saved .. best regards and, as usual, a big thank You for answering Horst ___ use-revolution mailing list use-revolution@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-tp15529083p15535637.html Sent from the Revolution - User mailing list archive at Nabble.com. ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: UPDATE table and quote in a text
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@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: UPDATE table and quote in a text
Good morning Trevor, Thanks for that. I´ll try tomorrow and let you know here best regards 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@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-tp15529083p15540022.html Sent from the Revolution - User mailing list archive at Nabble.com. ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
