;+ ; NAME: ; quote ; PURPOSE: (one line only) ; Convert a string into one safe for including in a mySQL query ; DESCRIPTION: ; Similar to the mysql function quote. Leading and trailing blanks ; are removed. Leading and trailing quotes are preserved but are considered ; to be delimiters for the string provided there is a leading and a trailing ; quote. If there is just one it is considered to be interior to the string. ; An interior single quote is converted to \' and a backslash is converted ; to \\. Embedded newlines (string(10B)) are quoted with '\n' where n is ; the letter n. ; On output, the string will have leading and trailing quotes added ; if they weren't already there. However, if the string is a scalar ; and it is 'NULL' (4 characters) after stripping blanks ; it is returned as is without quotes. ; The input can either be a single string or a string array. If an array, ; the strings are joined with a quoted newline separator. For example, ; s = quote(['this text output','more of the output']) produces ; " 'this text output\nmore of the output' " as a scalar string. This ; feature exists to support comment fields of type TEXT or BLOB. ; In multiline cases (input is a string array or contains 10B newline ; characters) empty or all whitespace lines at the begining or end ; of the total string will be trimmed. A single such line comes back as ; a ''. ; CATEGORY: ; Database ; CALLING SEQUENCE: ; result = quote(string) ; INPUTS: ; string - String to be processed- if a string array, the strings are ; joined with a newline quoted after each string including the ; last. Embedded newline characters (10B) are also quoted in ; this way. ; OPTIONAL INPUT PARAMETERS: ; KEYWORD INPUT PARAMETERS: ; LITERAL- Flag, if set, some of the processing of quote is not done, ; specifically removal of trailing/leading blanks, special ; treatment for the string 'NULL', and removal of leading and ; null/whitepace lines in the multiline cases. The processing ; of single quotes and backslashes in the input string is the same. ; OUTPUTS: ; return value is a string that is ready for mySQL ; KEYWORD OUTPUT PARAMETERS: ; COMMON BLOCKS: ; SIDE EFFECTS: ; RESTRICTIONS: ; Newlines are currently quoted with two characters: '\n' where the ; n is a literal lower case n. It is inadvisable to try to use an ; actual '\n' within the string to be quoted, although it will work ; currently if the string is a scalar and contains no embedded 10B characters. ; PROCEDURE: ; MODIFICATION HISTORY: ; Written by Marc W. Buie, Lowell Observatory, 2003/08/12 ; 2006/10/27, Peter L. Collins, Lowell Observatory ; add support for string array input (with newlines) ; 2006/11/28, PLC, allow literal (string(10B)) newlines in str. ; 2006/12/11, PLC, disconnect LITERAL flag from rank of str and ; (except if LITERAL) trim leading and trailing ; null or all whitespace lines in the multiline cases. ;- function quote,str,LITERAL=literal self='QUOTE: ' if badpar(str,7, [0,1], caller=self+'(str) ',RANK =strrank) then return,'' if badpar(literal,[0,1,2,3], 0, caller=self+'(LITERAL) ', $ default=0) then return,'' strvector = (strrank eq 1) multiline = strvector t = "'" ; trim leading and trailing blanks if not literal then newstr = strtrim(str,2) $ else newstr = str ; check for special value NULL if not literal and not strvector and newstr[0] eq 'NULL' then return,newstr ; are there leading and trailing single quotes? If so, trim them off sizeable = where(strlen(newstr) ge 2) if sizeable[0] ge 0 then begin strippable = where( strmid(newstr[sizeable],0,1) eq t and $ strmid(newstr[sizeable],0,/REVERSE_OFFSET) eq t) if strippable[0] ge 0 then begin strippable = sizeable[strippable] newstr[strippable] = strmid(newstr[strippable], 1, $ strlen(newstr[strippable])-2) endif endif ; find all single quotes and \ and prepend a \ to each. patt = "('|\\)" for j = 0, n_elements(newstr)-1 do begin ; use the regex feature of strplit to find all pattern match ; boundaries pos = strsplit(newstr[j],patt,/regex,LENGTH=len,/PRESERVE_NULL) if n_elements(pos) gt 1 then begin ; join the substrings back appending "\" and the delimiting "'" or "\" ; for the final substring, this is a no-op (pos + len out of range) ; based on how strmid works, but there is a spurious '\' generated ; which is excised. newstr[j] = strjoin(strmid(newstr[j],pos, len) + '\' + $ strmid(newstr[j], pos + len,1)) newstr[j] = strmid(newstr[j], 0, strlen(newstr[j])-1) endif if strpos(newstr[j],string(10B)) ge 0 then begin multiline=1 ; quote any embedded literal newlines (string(10B)) strnl = strsplit(newstr[j], string(10B),/EXTRACT,/PRESERVE_NULL) newstr[j] = strjoin(strnl, '\n') endif endfor ; add the quoted newlines and make into a scalar string. if strvector then newstr = strjoin(newstr, '\n') if multiline and not literal then begin ; processing to remove leading and trailing empty lines. substr = strsplit(newstr, '\\n',/EXTRACT,/PRESERVE_NULL,/REGEX) pos1 = -1 for j = 0, n_elements(substr) - 1 do begin if substr[j] ne '' then begin pos2=j if pos1 lt 0 then pos1=j endif endfor if pos1 ge 0 then newstr = strjoin( substr[pos1:pos2],'\n') $ else newstr = '' endif newstr = t + newstr + t ; wrap in single quotes return,newstr end