Ways to counter SQL Injection

Here are a few suggestions to counter the problem of sql injections.

Database Permissions

Set the permissions on the database username / password as tightly as possible.  If you are displaying data, there is no need for the user to have insert or update permissions into the database.  One solution is to have two usernames / passwords.  One would have select permissions, and would be used only for display.

The other would have select, insert and update permissions used only for forms that require data to be stored in the database.

Test all data input

All form data and all url query strings should be tested.

For example, if you are passing data using a query string any record id’s are usually integer, so test that they are actually integer values with a function such as isumeric in classic ASP.

Use correct data types and data sizes in the databaseThis means that if you have a colunn which is a persons name, the data type size only needs to be 40 characters.

There is no need to have a data size any larger than required.

Convert text to htmlBefore storing text in a database, convert it into html.  This will change inputs such as the Javascript <script> to its html equilivant which cannot be executed on a web page.

Filter out any characters that may cause issues. and are not required.

Use parameterized queries

If you use parametized queries for connection to the database you eliminate string concatenation.  You should always use parametized queries rather than constucting the sql.

Check characters particlarly with username / password

If an entry is a username, it normally does not require any other characters other than a to z and 0 to 9 and it only needs to be say, 8 characters long.