SQLi is still in service

For hacking TalkTalk, which led to the theft of personal data of about 150,000 users, a vulnerability was discovered that was discovered even before its birth. This attack method was SQL injection (SQLi). In this type of attack, hackers usually enter malicious commands into forms on a website in order to force the site database to provide the necessary data.



This method was used to steal personal data of employees of the World Health Organization, the theft of the Wall Street Journal data and hacking the websites of US federal agencies.

This is the easiest way to hack the site.

This is an expression of one of the hackers, known under the pseudonym w0rm and responsible for hacking the Wall Street Journal. The invasion then took only a few hours.
')
For all its simplicity, SQLi at the same time is highly efficient for obtaining digital data of corporations and even government sites.

SQL injections were a scourge for the Internet era. Year after year, they are referred to as one of the best security vulnerabilities responsible for countless data theft on the Internet.

Jeff Forristal was probably the very first to publish a hacking instruction and how to protect a server on Windows NT using SQL injections in the Phrack hacker log. Jeff is currently heading the cybersecurity director position at BlueBOX Security.



SQL or Structured Query Language is a programming language used to manage databases. In essence, it is used when the site has to call a piece of information from its database, or process it or present it to the user. But Forristal concluded that entering certain commands would force the server to show the information stored on it.

People can combine SQL commands.

In the December 1998 issue of Phrack, Forristal wrote about a series of problems with the Microsoft SQL server version. When Forristal's colleague reported this to Microsoft,

Their response was quite funny.

The fact that you can read it does not carry any threat, so do not worry and take hasty measures to stop it.

More than 15 years after this vulnerability was publicly described, SQLi repeatedly won first place in the Top 10 vulnerabilities from OWASP, which appeared every three years. The Open Web Application Security Project (OWASP) is a non-profit organization that tracks the various threats that websites face.



SQL injections are still the number one risk for many web projects.

When you go to a web page, you execute a query that is analyzed and after that you are provided with the data in accordance with the query.

A small example is when an application uses unreliable data in building a subsequent call to vulnerable SQL:

String query = "SELECT * FROM accounts WHERE custID='" + request.getParameter("id") + "'"; 

Likewise, blind trust from an application to the platforms can lead to vulnerable requests.
For example, Hibernate Query Language (HQL):

 Query HQLQuery = session.createQuery(“FROM accounts WHERE custID='“ + request.getParameter("id") + "'"); 

In both cases, the attacker modifies the value of the “id” parameter in the browser to send: 'or' 1 '=' 1.
For example:

 http://example.com/app/accountView?id=' or '1'='1 

This changes the meaning of both requests, which makes it necessary to give all the records from the table of accounts. More dangerous attacks may alter data or even trigger stored procedures.

One-time attack can provide one piece or section of information, which means that the attacker will need to repeat such requests until he receives all the data from the database. Naturally, this approach takes a lot of time. Thus, hackers can use tools that automate this process by executing queries instead. Havij has become quite a popular script script script because it is well optimized for Windows and has a convenient GUI (graphical user interface). The other part uses the SQLMAP scanner released in 2006. But more rapidly SQLMAP began to develop only when Miroslav Shtampar, a developer from Croatia, and Bernardo Damele, an information security consultant from Italy, joined the work on it. This scanner, like a search engine bot, searches for input forms on a web page and presents forms with data that could cause a MySQL syntax error.

An example of how Troy Hunt (Troy Hunt), the founder of haveibeenpwned.com, teaches his son to use SQLi with Havij.


Finding a victim for attack is also easy to automate. Typically, attackers use the most popular Google search engine to search for URLs that are associated with scripts vulnerable to SQLi. The attacker has scripts that automatically check all URLs for vulnerabilities. This process is so simple that it can teach even a child. There are also enough tutorials on YouTube on how to commit a SQLi attack.



But there are ready-made solutions to stop SQLi attacks and they can be deployed by site developers to avoid consumer or corporate data leakage. These solutions have existed for many years.

One of the easiest ways to solve this problem is to use “prepared queries”. In this case, the SQL commands that control the database cannot be sent through the user login form.

Prepared queries are used to perform the same (or similar) SQL queries repeatedly with high efficiency.

Prepared queries basically work like this:
1. Preparation: A SQL query template is created and sent to the database. Some values ​​are not specified, are called parameters and are marked with "?". Example: INSERT INTO MyGuests VALUES (?,?,?)
2. The database parses, compiles and performs query optimization on the SQL query template and saves the result without executing it.
3. Execution: Later, the application associates values ​​with parameters and the database executes the query. An application can execute a request as many times as required with different values.

Compared to the execution of direct SQL queries, prepared queries have three main advantages:

• Prepared requests reduce the time of parsing, since the preparation of a request is done only once (although the request itself can be executed several times).
• General parameters save the channel and traffic to the server, since you need to send only the parameters, and not the entire request.
• Prepared queries are very useful against SQL injections, since the values ​​of the parameters that are transmitted later using another protocol will not fully correspond to those sent. If the original query is different from the query from the external input, the SQL injection will not be executed.

Prepared queries in MySQL

 <?php $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDB"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } // prepare and bind $stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)"); $stmt->bind_param("sss", $firstname, $lastname, $email); // set parameters and execute $firstname = "John"; $lastname = "Doe"; $email = "john@example.com"; $stmt->execute(); $firstname = "Mary"; $lastname = "Moe"; $email = "mary@example.com"; $stmt->execute(); $firstname = "Julie"; $lastname = "Dooley"; $email = "julie@example.com"; $stmt->execute(); echo "New records created successfully"; $stmt->close(); $conn->close(); ?> 

In this SQL, a question mark (?) Is set where we want to place an integer, string, double, or blob value.

 "INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)" 

Now let's look at the bind_param () function:

 $stmt->bind_param("sss", $firstname, $lastname, $email); 

This function binds the parameters to the SQL query and tells the database what parameters to enter. The “sss” argument restricts the type of data that can be specified for the listed parameters.

The argument can be one of four types:

i - integer
s - double
s - string
b - BLOB

By telling MySQL what type of data to expect, we minimize the risk of SQL injection.

Preparing a PDO request

 <?php $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDBPDO"; try { $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); // set the PDO error mode to exception $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // prepare sql and bind parameters $stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (:firstname, :lastname, :email)"); $stmt->bindParam(':firstname', $firstname); $stmt->bindParam(':lastname', $lastname); $stmt->bindParam(':email', $email); // insert a row $firstname = "John"; $lastname = "Doe"; $email = "john@example.com"; $stmt->execute(); // insert another row $firstname = "Mary"; $lastname = "Moe"; $email = "mary@example.com"; $stmt->execute(); // insert another row $firstname = "Julie"; $lastname = "Dooley"; $email = "julie@example.com"; $stmt->execute(); echo "New records created successfully"; } catch(PDOException $e) { echo "Error: " . $e->getMessage(); } $conn = null; ?> 

The advantage of prepared queries is that they set the semantics of the query in such a way that any input data could not dazzle the developer by including a syntax that alters an incorrect query designed to get a string that extracts data from arbitrary tables. Another way involves using a SQL library that cares about processing incoming requests to them. Roughly speaking, it cleans up all the data entered by the user in the query, which can be potentially dangerous.

If SQLI is so simple that even a child can do it. And the solutions are pretty simple. But why then are SQLI-based attacks still successful?

Any serious programmer should know about SQLi, but in fact finding a really good programmer in a foreign market is not an easy task. This is confirmed by personal experience. My friend almost a year ago went to work in a foreign project. He considered himself to be a rather mediocre programmer (by the way, his acquaintances, IT experts, and ex-colleagues also considered his friends in the same way). But he was pleasantly surprised that he was the best at his new job. He is still on his project is considered a highly qualified specialist. That is why foreign companies hire mediocre programmers, even if they have no idea about the options for mitigating basic types of vulnerability.

But in addition to this, under the pressure of their top management, such programmers are developing more functional software, and not more secure.

Ultimately, the responsibility for the security of these sites and the data they contain are reduced to the web developers themselves.