Better Solution - Prepared statements (Parameterized Queries). SQL query is precompiled with placeholders. Data is added in at run-time, converted to correct type for the given fields. Issues with Prepared Statements Cannot use them in all situations. Generally limited to replacing field values in SELECT, INSERT, UPDATE, DELETE statements. Example: if also asking user for information that determines choice of table name, cannot use a prepared statement. Additional Precautions Do not access the database as privileged user, attacker who gains access through user will have that user's privileges. Limit database user to only what they need to do, e.g. reading information from database, no insert/update/delete. Do not allow direct access to database from the internet, requires user to go through your(controlled) applications. Do not embed database accounts passwords in your code, encrypt and store them in a repository that is read at application startup. Do not expose information in error messages, e.g. do not display stack traces. Applications Issues Be aware of how information is transmitted between client applications and database. Student Research Project at UWEC tested 5 DBMSs and various clients (vendor and user) Most common client applications (vendor-supplied or user-programmed) at least encrypt the connection password. Some clients encrypt the connection user Certain DBMSs have varying levels of security (e.g. PostgreSQL) One DBMS transmits the connection password length (MS SQL Server 2005 Edition) Be aware of how application exposes information.
Better Solution - Prepared statements (Parameterized Queries). SQL query is precompiled with placeholders. Data is added in at run-time, converted to correct type for the given fields. Issues with Prepared Statements Cannot use them in all situations. Generally limited to replacing field values in SELECT, INSERT, UPDATE, DELETE statements. Example: if also asking user for information that determines choice of table name, cannot use a prepared statement. Additional Precautions Do not access the database as privileged user, attacker who gains access through user will have that user's privileges. Limit database user to only what they need to do, e.g. reading information from database, no insert/update/delete. Do not allow direct access to database from the internet, requires user to go through your(controlled) applications. Do not embed database accounts passwords in your code, encrypt and store them in a repository that is read at application startup. Do not expose information in error messages, e.g. do not display stack traces. Applications Issues Be aware of how information is transmitted between client applications and database. Student Research Project at UWEC tested 5 DBMSs and various clients (vendor and user) Most common client applications (vendor-supplied or user-programmed) at least encrypt the connection password. Some clients encrypt the connection user Certain DBMSs have varying levels of security (e.g. PostgreSQL) One DBMS transmits the connection password length (MS SQL Server 2005 Edition) Be aware of how application exposes information.
database_security_issues.docx