Advanced SQL Database Programmer phần 9

provides fee-based access to publications, unauthorized logins could be dismissed as lost revenue. The rationalization is that the cost to impose additional security features outweighs the cost of lost subscriber revenue. After all, there is a high probability that a person who hacks into a fee-based publication service won’t pay to access the site if the hacking attempts fail. Such reasoning is naive and fatally flawed. What if the SQL savvy hacker decides to inject completely new SQL statements? Consider the following SQL code: ` or 1=1;update prices set cost = 0-- Once again, the SQL Profiler reveals what was actually executed, which is actually two separate SQL statements: select count (*) from employees where LastName = `` or 1=1 update merchandise set price = 0 --` and FirstName = `` A semicolon is a valid SQL character for separating one SQL statement from another. It is particularly useful when multiple statements are entered on a single line or into a single string. A semicolon tells the SQL parser that the complete string is comprised of individual SQL statements to execute separately. The hacker is not limited to injecting DML statements (insert, update, delete). How about a drop table statement? Assuming that the application has rights to drop tables, drop table statements could be injected to remove tables from the database. Consider the following input: ` or 1=1;update prices set cost = 0;drop table audit_trail;shutdown-- DBAzine.com 87 BMC.com/oracle Not only would the audit_trail table be dropped, but the database would be shutdown immediately afterwards. Prevention Through Code To provide the absolutely most effective security, multiple techniques are required to protect your databases. The first line of defense is prevention at the user interface. Whenever you are working with a database, you must first understand your data so you will better be able to protect it. In the test program, the LastName column of the Employees table is used as if it were a password in a table of usernames. This column has a maximum length of 20 characters, yet the test program does not limit user inputs to 20 characters. This is an egregious oversight: The worst attacks illustrated in this article could easily have been prevented by limiting the input to 20 characters. Not all input fields are short, so input length checking is only part of an overall defense. Additionally, in this example, a length restriction would not prevent this attack: ` or 1=1;shutdown-- Assuming that characters such as semicolons and double dashes are not valid in a username, then regular expression validation can be used to detect the invalid characters and reject the input. Not only is restricting the set of valid input characters a Procrustean solution, there exists the possibility of a very clever exploit using the SQL char function to provide the value of an individual ASCII character without explicitly having the character in the injected SQL input. Despite the limitations of rejecting input based on certain characters, it should be used when it is appropriate. Visual Studio.NET has a 88 DBAzine.com BMC.com/oracle regular expression validate control that greatly simplifies using regular expressions in ASP.NET Web pages. Data type checking is helpful in detecting rogue input. User interfaces often accept date, time, and numeric input in text fields. Although users can type whatever they want in a text field, programs can check the input data to see if it is the correct data type. For example, if the Password input box is mapped to the EmployeeID column, then any user input should be checked to see if it is integer data. Only if the input is of the correct data type would the input be passed to the database server for processing. All of the rogue statements shown would fail an integer data type validation check. The fundamental flaw of dynamic SQL is not that rogue inputs are allowed, but that rogue input can be executed. Dynamic SQL is convenient for developers, but it does not lock down the actual SQL during the application design stage. Prevention Through Stored Procedures Stored procedures are compiled when they are created; the SQL statement is frozen at creation time. Using the first rogue SQL fragment of ` or 1=1-- with the Stored Proc Login button, SQL Profiler reveals what is actually executed: select @NbrRows = count(*) from employees where LastName = @Username and FirstName = @Password Understand that @Username contains the following characters: ` or 1=1-- DBAzine.com 89 BMC.com/oracle No matter what the inputs for @Username and @Password are, the stored procedure will always execute only the select statement shown. The SQL statement is predefined; it will never change based on the inputs. This stored procedure accepts two inputs, both strings. No matter what those input strings contain, they are always treated as just strings. Even a semicolon is treated as just another character, not as a SQL statement separator. Although stored procedures overcome the fundamental weakness of dynamic SQL, it comes at a price. A stored procedure must be written in advance for all possible queries, and this is not always practical. For example, a search page for real estate listings does not lend itself to stored procedures. A customer is presented with multiple search criteria (price, number of bedrooms, bathrooms, and so on). Not all search criteria would be used at all times, so the number of stored procedures required to accommodate every possible select string would be unwieldy. Dynamic SQL is required in such cases. Coding stored procedures in the .NET environment is covered in "Calling Stored Procedures from ADO.NET." (http://www.dbazine.com/cook6.html) Prevention Through Least Privileges The most basic security concept of all is the principle of least privileges. Never grant any more privilege to a user or an application than the absolute minimum to accomplish a task. Typical end user applications should not allow application users to execute indiscriminate DML, drop tables, or shut down databases. A hacker who attempts to drop a table but does not have rights to do so will not succeed in the attempt. 90 DBAzine.com BMC.com/oracle Conclusion Implementing security best practices can prevent unintended access to your database. Forethought and well-designed applications are instrumental in protecting your interests. While dynamic SQL has its uses, a determination should be made early on as to whether or not it would be the best choice. If possible, stored procedures should be considered early in the design stage, as their execution is not dependent on nor changed by user input. Code should also be thoroughly examined to see that it does not lend itself to invasion. Developers must think like a hacker in order to fully evaluate the weaknesses in their applications. DBAzine.com 91 BMC.com/oracle ... - tailieumienphi.vn