IT Security Tutorial - Preventing SQL injections

[Music] sql injection is an attack that occurs when untrusted data is used to construct an sql query the data is inserted or injected into the sql query string it allows an attacker to execute arbitrary requests to an sql database sql injection sometimes called sqli for short is not the only form of code injection but it is the most common most modern web applications rely heavily on databases and by some estimates 75 percent of databases use sql the same general principles we discuss for sqli can be applied anytime untrusted data is used to construct a value that's used for code or in a query owasp the open web application security project has consistently ranked code injection as the top security threat to web applications for the last 10 years so this subject is definitely worth our attention sqli is easy for attackers to detect and to exploit let's look at an example of sql injection imagine a login form when the form is submitted the application code constructs an sql query to search for a matching username and password in the users table be sure to notice where the username variable is inserted into a string and how it appears in the resulting sql now imagine that a hacker visits the login form instead of providing a username the hacker submits a carefully crafted string in its place read the resulting sql carefully notice the single quote right after sqli when it's inserted into the string that single quote closes the opening single quote in order to break out of the intended input value the fragment or one equals one well that will always return true and the double dashes indicate the start of a comment in sql the result is that the query will match all users and the password clause would be ignored potentially this sqli attack could bypass the login page and grant access without the correct credentials sqli attacks can have many different goals they can be used to probe the structure of a database they can be used to steal data this is probably the most common goal to steal personal information passwords credit cards or anything in the database that has value they can add or change database data perhaps to place an order or to elevate their access privileges they may be used destructively for truncating or dropping tables or as we saw in our example it may be possible to trick an authentication test so that it evaluates to true without needing the correct credentials a well-known xkcd comic strip has fun with this idea the school calls a mother and says they're having computer trouble they ask if she really named her son robert single quote parentheses semicolon drop table student semicolon dash dash she says oh yes little bobby tables we call him his name has caused an sql injection which erased database records at the school the mom says and i hope you've learned to sanitize your database inputs any query which utilizes user input is vulnerable to sqli the most frequent area of attack are sql where clauses they're used to locate records which match parameters and they incorporate a lot of data from the current request other query types are equally vulnerable just not as frequently used insert update and delete statements must be considered as well as other sql clauses like select and order by one variation on sql injection is called blind sql injection a vulnerability may exist on a page but it does not change the response to the browser that means there would be no feedback to tell an attacker if it succeeded a clever technique is to inject an sql query which will cause the database to pause or to return a slow response if the injection works the easiest version is to use sql's sleep function to create a slow response if it works the server will pause for 5 seconds this tells an attacker that a vulnerability exists then they can execute other commands blindly here's a more elaborate example which when injected will join a new query to the original one and then use if to test if the first character of a username is char 97 which is the letter a if it's a then the benchmark function executes a slow process 5 million times otherwise it finishes quickly an attacker could go through the entire alphabet in a loop to determine the full username this technique can be used to examine the structure of the database table names column names as well as the values in the table rows the attacker does not need to even see the text they can just play guessing games and use fast or slow responses to know when they've guessed right so what are the solutions to sql injection first you should limit the application's database privileges your application needs to read and write to the database but it probably does not need permission to create drop or truncate tables it probably should not be able to grant access privileges to database users and you should never let the application connect as the root user who can do anything including creating new user accounts if you use the principle of least privilege it will limit the damage that an sql injection can cause the most important prevention is to sanitize the input you should always escape data for sql before using it in a query string in simple terms sql sanitization means rendering every single quote in the data harmless by putting a backslash in front of it but single quotes are not the only danger and there are many tricks to avoid detection most programming languages for the web have an sql sanitization library that you can use use one of those they offer better sanitization than if you tried to write your own function one of the best preventions is to use sql prepared statements in a prepared statement the sql query is prepared with placeholders for dynamic data it's like creating a program which expects to receive data as inputs when it's run the data must match a specified data type such as string or integer and because the sql query has already been mostly written the data can't interfere with it it's very effective here's an example the first line sets a variable called sql to a query string the question mark after title indicates a placeholder for dynamic data the second line converts it into a prepared statement making it like a program that's ready for input the third line adds a value as input and executes the statement this example is using only sql but most web programming languages include functions to make it easy to use sql prepared statements a third prevention is to use allow lists and to validate data before using it in a query this prevention is less powerful than using sql sanitization or sql prepared statements but it can be used alongside them to provide defense in depth [Music] you

Channel: LinkedIn Learning

Views: 488,952

Keywords: sql tutorial for beginners, sql, sql tutorial, learn sql, sql injection, sql injection attack, what is sql injection, blind sql injection

Id: S4qtRFsuKVY

Length: 7min 53sec (473 seconds)

Published: Tue Mar 02 2021