addslashes

[Brian K. White]

On 8/25/2010 4:39 PM, richard wrote:
> I have a need to create some sql queries using filepro.
>
> My problem is that some of my text includes apostrophes and other chars.
> that "break" the query.
>
> In php there is a command called "addslashes".
>
> Is there a simple way in filepro to turn "Robert's golf clubs" into
> "Robert\'s golf clubs"?
>
> Richard D. Williams
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL: http://mailman.celestial.com/pipermail/filepro-list/attachments/20100825/ca982bc9/attachment.html
> _______________________________________________
> Filepro-list mailing list
> Filepro-list at lists.celestial.com
> http://mailman.celestial.com/mailman/listinfo/filepro-list
>


Adapted from http://www.aljex.com/bkw/filepro/prc_urlenc.html

         If:' data-in data-out
       Then: declare extern c_di, c_do

         If:' position length inchar outchar data-in-temp '' do not 
define size for c_oc or c_dit
       Then: declare c_dp(8,.0), c_dl(8,.0), c_ic(1), c_oc, c_dit

         If:
       Then: c_dp = "1" ; c_do = ""

         If:
       Then: c_dit = c_di{"" ; c_dl = len(c_dit)

nxtchr  If:
       Then: c_ic = mid(c_di,c_dp,"1") ; c_oc = c_ic

         If:"$&+,/:;=?@ <>#%{}|^~[]`'" { chr("92") { chr("34") co c_ic
       Then: c_oc = chr("92") & c_ic

         If:
       Then: c_do = c_do & c_oc

         If:c_dp lt c_dl
       Then: c_dp = c_dp + "1" ; goto nxtchr


----------------------


It's written as a call table, where you would save it in a filepro file, 
I use one called "lib", so lib/prc.addslashes, and in other tables in 
other files you would do:

   If:
Then: declare global c_di, c_do
   If:
Then: c_di = "input string" ; call "lib/addslashes" ; show c_do

But you could change it to use regular dummies and take out the declares 
and use it as a gosub easy.


This off the cuff adaptation is not tested but the table I referenced is 
very well tested and the change was tiny so it should be fine.

This:

"$&+,/:;=?@ <>#%{}|^~[]`'" { chr("92") { chr("34")

is all the characters it will escape. Modify to suit since you may want 
more or fewer things munged. Thislist was specifically for making 
strings url-safe from browsers.


Since writing that originally, as the blacklist string got longer and I 
kept running into things it didn't handle, like form feeds, instead of 
continuing to add chr("vet another value") to the line,
here's another way that instead of defining a blacklist in a litteral, 
it defines a whitelist and uses the fact that the _really_ safe chars 
are all in 3 contiguous blocks of ascii values. The advantage is it 
works on every possible character including all those high ascii binary 
junk that would be impossible to put in a litteral in filepro and would 
be impractical to express as 127 chr("n") commands. Since this was a 
url-encoder originally, and a binary character is perfectly ok to 
express as %hexvalue, I don't know if the same applies for merely 
backslash-escaping things for sql queries. I would expect stuff like 
that to simply be disallowed, or require other encoding like 
backslash+octalvalue or something.

If:
Then: declare extern c_di, c_do
If:
Then: declare c_dp(8,.0), c_dl(8,.0), c_ic(1), c_oc, c_dit, c_ic_d(3,.0)
If:
Then: c_dp = "1" ; c_do = ""
If:
Then: c_dit = c_di{"" ; c_dl = len(c_dit)

nxtchr
IF:
Then: c_ic = mid(c_di,c_dp,"1") ; c_oc = c_ic
If:
Then: c_ic_d = asc(c_ic)
If: c_ic_d ge "48" and c_ic_d le "57"     ' 0-9
Then: goto cat
If: c_ic_d ge "65" and c_ic_d le "90"     ' A-Z
Then: goto cat
If: c_ic_d ge "97" and c_ic_d le "122"    ' a-z
Then: goto cat
If:
Then: c_oc = chr("92") & c_ic_d

cat
Then: c_do = c_do & c_oc
If: c_dp lt c_dl
Then: c_dp = c_dp + "1" ; goto nxtchr


Perhaps for your situation the best answer is the first example, but 
just add this to it,

take this line:

         If:
       Then: c_do = c_do & c_oc

And change it to this

         If: asc(c_ic) lt "127"
       Then: c_do = c_do & c_oc

That way, the blacklist characters handle all the bad chars in the 0-127 
ascii range, and everything above 127 is thrown away.

Or some combination of both ideas because the first example with this 
addition probably still leaves a few ascii control characters that you 
need to either escape or throw away, these are all down in the 0-45 
range, things like form feeds and backspaces etc.

-- 
bkw