APG

The SQLClean package is a standard package for sanitizing SQL. All SQL Statements must be built via this package.
Any extra sanitization functions should be global functions, and should be included in this package. Please route all suggestions to Sean.

Functions

  • BuildSQL – Used to Build SQL statements with parameters
  • LikeString – Used to build up a string for usage in Like.

BuildSQL Usage

Function BuildSQL Global String sCommand Variant sParam1 
Variant sParam2 Variant sParam3 Variant sParam4 Variant sParam5 
Variant sParam6 Variant sParam7 Returns String

Call BuildSQL with the parameters indicated in the SQL statement as %1, %2, etc and the parameters passed as extra function parameters. Please note that single quotes are automatically added to strings and dates and should not be part of your SQL statement.
e.g.

Move (BuildSQL("Select * from contact where name = %1", "THE NAME")) to sSQL

If you’re passing a Date, or an integer as a parameter, it must by typecast before being passed to the BuildSQL function.
e.g.

Move (BuildSQL("Select * from contact where order_date= %1", (Date("03/20/2019")))) to sSQL

LikeString Usage

Function LikeString Global String sFront String sInp String sBack String sEscapeChar Returns String

LikeString allows you to create a String for use in a Like statement. It takes the following parameters,

  • sFront. Blank if You don’t want to wildcard the front of the statement, “%” otherwise.
  • sInp. The text you’ll be searching for.
  • sBack. Blank if You don’t want to wildcard the end of the statement, “%” otherwise.
  • sEscapeChar. The character to use as an escape character.

Examples

   (LikeString("%","END","","/")) ; Search for strings ending with "END" (/ is escape character)
   (LikeString("","B","%","/")) ; Search for strings beginning with a "B" (/ is escape character)
   (LikeString("","%","%","/")) ; Search for strings beginning with a "%" (/ is escape character)
   (LikeString("%","XX","%","/")) ; Search for strings containing "XX" (/ is escape character)

I’m not sure of the interface to this, so if you have suggestions – let me know.