Jump to content

Best Practices for Input Variables

Recommended Posts

ANY variable used in SQL statements should be handled carefully. YOU MUST ALWAYS USE THE MYSQLENCODE FUNCTION.
Failure to do this can make your script vulnerable to SQL Injection. At best it will just fail to work. At worst your server and all of your clients will be compromised, your company will fail and you will have alot of uncomfortable discussions.  Just Use "Script String Functions: MYSQL Encode" every time....

Step 1, SET: @encInputRenderedHarmless@ = MYSQLENCODE(@MaliciousInputThatWillDestroyEverything@)
Step 2, SET: @encTypicalStringInput@ = MYSQLENCODE(@TypicalStringInput@)

I prefer to prefix the output variable with "enc", so that I have the original value (for Script Log, etc.) and the encoded value for use in queries.

With that important message out of the way, on to more typical Script Variable tips.

A common need is to determine if a variable has a value or not. When a variable doesn't exist it is not blank, but when it is blank it has no value. This means that you cannot just check if the variable is = or Not = to blank. The following three step sequence can be used to ensure that a variable exists and has a known value before checking what the value is.

Step 1, IF  @VariableToCheck@  Not Exists    THEN  Jump to line 3
Step 2, IF  @VariableToCheck@  Not =    THEN  Jump to line 4
Step 3, SET:  @VariableToCheck@ = MyDefaultValue
Step 4, Note: VariableToCheck will now always have a default value or the existing value assigned

If you want to treat undefined as blank, just use Step 3 to set VariableToCheck to be blank.

Another common need is to check if a variable has a certain numeric value or is greater than or less than another number. The "Script Regex" function can be used to ensure that a value is either a valid number, or is blank. You can then check if it is blank or not, before testing the value.

Step 1, SET: @VariableToCheck@  =  MATCH(@SignedIntegerVariable@ PATTERN \d+), Continue On Failure
Step 2, SET: @VariableToCheck@  =  MATCH(@UnsignedIntegerVariable@ PATTERN -?\d+), Continue On Failure
Step 3, SET: @VariableToCheck@  =  MATCH(@FloatingPointVariable@ PATTERN -?\d*\.?\d+), Continue On Failure
Step 4, IF  @VariableToCheck@  =    THEN  Jump to :NotANumber, Exit On Failure

It is very important that the Script Regex steps be set to Continue On Failure, so that a blank value is assigned if there is no match.

Sometimes the input can be ambiguous. Script Regex can be used to match multiple "valid" responses, and will return a blank for anything else. So if you have a parameter variable such as "ForceOperation", does the technician enter "True", "Yes", "Y", "1", or what? You can determine if they entered any of those options or not, and set the variable to specific values for True or False.

Step 1, SET: @ForceOperation@  =  MATCH(@ForceOperation@ PATTERN (?i)^(True|Y|1)), Continue On Failure
Step 2, IF  @ForceOperation@  Not =    THEN  Jump to line 4, Exit On Failure
Step 3, SET:  @ForceOperation@ = 0, Exit On Failure
Step 4, IF  @ForceOperation@  =  0  THEN  Jump to line 6, Exit On Failure
Step 5, SET:  @ForceOperation@ = 1, Exit On Failure
Step 6, Note: ForceOperation will now always be 0 or 1., Exit On Failure

If you don't need the value later you can just do this:

Step 1, SET: @ForceOperation@  =  MATCH(@ForceOperation@ PATTERN (?i)^(True|Y|1)), Continue On Failure
Step 2, IF  @ForceOperation@  =    THEN  Jump to :DoNotForceOperation, Exit On Failure
Step 3, Note: The Operation has been forced, Exit On Failure

Script Regex can also be useful for cleaning up variables by removing leading and trailing blanks or ensuring only the first line is kept, with no CR/LF characters.

Step 1, SET: @TrimmedVariableWithMultipleLines@  =  MATCH(@UnknownInput@ PATTERN (?![\r\n\s]).+(?<![\r\n\s])), Continue On Failure
Step 2, SET: @TrimmedVariableWithSingleLine@  =  MATCH(@UnknownInput@ PATTERN (?![\s])[^\r\n]+(?<![\s])), Continue On Failure

Happy Scripting!

  • Thanks 1
Link to post
Share on other sites

Regarding the use of MYSQLENCODE(), could you provide an example usage AFTER MYSQLENCODE() has been used? I'm failing to see how this offers any protection, but this is because of my ignorance and inexperience, hence my question. :) Once you have encoded the input, how do you use the encoded input? Provide examples please - this will help with my understanding.

Link to post
Share on other sites

MYSQLENCODE just prepares a string for use inside MYSQL queries, by escaping characters with special meanings.  Ie., replacing ' with \'.

Given the following steps:

Step 1, SET: @TypicalStringInput@ = This is a normal sentence.
Step 2, SET: @MaliciousInputThatWillDestroyEverything@ = This normal sentence'); DROP DATABASE LABTECH;
Step 3, SET: @encInputRenderedHarmless@ = MYSQLENCODE(@MaliciousInputThatWillDestroyEverything@)
Step 4, SET: @encTypicalStringInput@ = MYSQLENCODE(@TypicalStringInput@)
Step 5, SET: @Test1@ = SQLRESULT[SELECT LENGTH('@TypicalStringInput@');]
Step 6, SET: @Test2@ = SQLRESULT[SELECT LENGTH('@encTypicalStringInput@');]
Step 7, SET: @Test3@ = SQLRESULT[SELECT LENGTH('@MaliciousInputThatWillDestroyEverything@');]
Step 8, SET: @Test4@ = SQLRESULT[SELECT LENGTH('@encInputRenderedHarmless@');]

The literal queries would be:

Step 5, SET: @Test1@ = SELECT LENGTH('This is a normal sentence.');
Step 6, SET: @Test2@ = SELECT LENGTH('This is a normal sentence.');
Step 7, SET: @Test3@ = SELECT LENGTH('This normal sentence'); DROP DATABASE LABTECH;');
Step 8, SET: @Test4@ = SELECT LENGTH('This normal sentence\'); DROP DATABASE LABTECH;');

The raw and encoded versions of TypicalStringInput are the same. There were no special characters to escape. But the MaliciousInput variable included a single quote, which needs to be escaped to be a literal single quote, otherwise it is a string terminator.  The encoded MaliciousInput when used is seen as part of 1 query in Step 8. The raw MaliciousInput in step 7 is seen as 3 queries, and you won't like the result from the second query.

Does that help?

See also https://bobby-tables.com/

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...