SQL Injection is the name given to manipulating SQL data through input objects. But How to Defend? Check it out Now in this Part 2 post!
If you have not yet read the post about “SQL Injection: Injecting Data from Inputs”, we advise you to take a look at it before keep reading this one. Understanding what SQL Injection is will help you to get the most out of this post below. Would you like to look now? Click here!
How to protect yourself from the well known SQL Injection
As everyone has seen in the previous post SQL Injection is the name given to manipulating SQL data through input objects.
From now on you will learn how to defend yourself from this deface in and out of our beloved Scriptcase.
Defense through Scriptcase
Few people know about it, but the scriptcase has a list of macros that allow the user to manipulate events, application buttons, security controls, perform operations with dates, etc. Among all these macros we can find the macro “sc_sql_injection”.
This is responsible for the safety of our input fields against the famous sql injection.
Let us now understand how it works.
Below we can see how the select behaves in relation to the data inserted in the input, using this select:
sc_select(rs, “SELECT * FROM sec_users WHERE login = ‘{user}’ AND pswd = ‘{pass}'”);
The image above shows details of what we had already seen in the previous post, but now watch the image below and see how the macro works inside our select:
sc_select(rs, “SELECT * FROM sec_users WHERE login = “.sc_sql_injection({user}).” AND pswd = “.sc_sql_injection({pass}));
So before without using the macro “sc_sql_injection” our code was open to interpretations, now with the use of the macro correctly, Scriptcase quickly realized that an abnormal value was being injected and placed a backslash before the value of the login field , This way the validation of the select would be:
SQL generated:
SELECT * FROM sec_users WHERE login = ‘\’ OR 1=1; — ‘ AND pswd = ”
Interpretation:
Bring me everything from the sec_users table where the login equals \ or 1 is equal to 1 and ignores everything to the right of the remainder comments.
Logically we will not have any login called “\”, therefore the person’s injection will be blocked.
So it is always important to use this macro to fetch or insert information into the database.
To learn more about how to use this macro go to: http://www.scriptcase.com.br/docs/en/v81/manual_mp.htm#macros-scriptcase/macros-scriptcase
#How to protect yourself out of Scriptcase, in pure PHP
In order to be free of the use of SQL Injection, certain measures must be taken. Some of the actions will be performed on the database server, others must be guaranteed by the source code, ie in our case PHP.
We can use the addslashes () function through PHP, which by the way is the same function used by the “sc_sql_injection” macro.
This function aims to insert a backslash before each single quotation mark and double quotation mark found in the last variable, this process is known as “escape”. If the PHP configuration directive “magic_quotes_gpc” is enabled, the escape is performed automatically on COOKIES data and data received through the GET and POST methods. In this case, it should not be done with addslashes (). The get_magic_quotes_gpc () function, available in PHP versions from 3.0.6, returns the current configuration of the magic_quotes_gpc directive. Here’s how to use this function:
Another way to protect yourself is also through MYSQLI in PHP. If you still use the mysql () functions, “RUN”, because in addition to being no longer used, it is totally vulnerable to such intrusions. In it, all queries are passed manually. But of course I would not talk to run from this lib without citing an alternative, the mysqli class, it uses parameters in bind so there is no direct concatenation and in this middle of the bind the class handles the input for nothing to function as a injection.
Here’s a quick example of how to use it:
Understanding the code:
<?php
# Here we instantiate the mysqli class, passing as parameter the server, user, password and name of the database.
$mysqli = new mysqli(‘server’, ‘user’, ‘password’, ‘database’ );
# With our class already instantiated, let’s make the method call prepare to “prepare” the query that will receive the values.
# Where you have “?” (Interrogation) will be the places where the values entered.
# Note: There is no need to worry about quotation marks in the case of strings.
$ Stmt = $ mysqli-> prepare (“SELECT name, email FROM sec_users WHERE login =? AND pswd =?”);
# After our statement is prepared with the query string and the questions, we will define which variables have entered
# In which places and their types through the function -> bind_param (“type string”, “variable in order”).
$ Stmt-> bind_param (‘ss’, $ login, $ password);
# Where you have ‘ss’ you should enter the initials of the variable type.
# Ex: $ login and $ password are strings, then ‘ss’. If it were $ value and $ password, then ‘ds’.
# Allowed types are:
# I – integer variables
# D – double variables
# S – string variables
# B – variables that provide data for a blob
# After defining which places the variables occupied, we will execute the statement.
If ($ stmt-> execute ()) {
# Another legal function of the statement is that we can do something similar to the inverse process of bind_param.
$ Stmt-> bind_result ($ name, $ email);
# The bind_result function will assign the values obtained in the queries, in our case name and email, in variables that we
# We chose the names, I preferred to keep the name, but could have put $ var1 and $ var2, where $ var1 would get the name
# And $ var2 the email.
# And now we get all the results by printing them on the screen with the variable names we defined in bind_result ().
While ($ stmt-> fetch ()) {
Echo “User name: $ name \ n User email: $ email \ n”;
}
}
?>
Obviously we will never be 100% protected, because if bigger companies like Facebook, Google and many others that invest millions in data security and are really efficient with respect to the subject, they manage to get rid of the hacker attacks, imagine us common programmers. But of course we can always make things difficult.
So that’s all folks, I hope you have enjoyed the post. I advise that for a better understanding they are read in the documentation of the functions and alternatives mentioned above.
Sources: http://imasters.com.br, http://www.devmedia.com.br, https://mathmesquita.me, http://php.net/
Original Post at http://scriptcaseblog.com.br/sql-injection-parte-2-como-se-defender/
You might also like…