Any suggestions? Export (0) Print
Expand All

Validating User Input

SQL Server 2000

  New Information - SQL Server 2000 SP3.

Always validate user input. Untested input can cause program errors, and may be used by hackers as a point of entry into your system. When implementing precautions against malicious input, consider the architecture and deployment scenarios of your application. Remember that programs designed to run in a secure environment can be copied to an insecure environment.

Best Practices

The following suggestions should be considered best practices:

  • Make no assumptions about the size, type, or content of the data received by your application. For example, evaluate:
    • How will your application behave if an errant, or malicious, user enters a 10-megabyte MPEG where your application expects a postal code?

    • How will your application behave if a DROP TABLE statement is embedded in a text field?
  • Test the size and data type of input, and enforce appropriate limits. This can help prevent deliberate buffer overruns.

  • Test the content of string variables and accept only expected values. Reject entries containing binary data, escape sequences, and comment characters. This can help prevent script injection and can protect against some buffer overrun exploits.

  • When working with XML documents, validate all data against its schema as it is entered.

  • Never build Transact-SQL statements directly from user input.

  • Use stored procedures to validate user input.

  • In multitiered environments, all data should be validated before admission to the trusted zone. Data that does not pass the validation process should be rejected, and an error returned to the previous tier.

  • Implement multiple layers of validation. Precautions you take against casually malicious users may be ineffective against expert hackers. The best practice is to validate input in the user interface, and then at all subsequent points at which it crosses a trust boundary.

    For example, data validation in a client-side application may prevent simple script injection; however, if the next tier assumes that its input has already been validated, any hacker capable of bypassing your client can have unrestricted access to your system.

  • Never concatenate user input that is not validated. String concatenation is the primary point of entry for script injection.

  • Do not accept the following strings in fields from which file names may be constructed: AUX, CLOCK$, COM1 through COM8, CON, CONFIG$, LPT1 through LPT8, NUL, and PRN.

  • When possible, reject input that contains the following potentially dangerous characters.
    Input character Meaning in Transact-SQL
    ; Query delimiter
    ' Character data string delimiter
    -- Comment delimiter
    /* ... */ Comment delimiters. Text between /* and */ is not evaluated by the server.
    xp_ Begins the name of catalog extended stored procedures such as xp_cmdshell.

See Also
© 2016 Microsoft