Prepared statements with MySQLi waits data entry validation?

Question

Prepared statements with MySQLi waits data entry validation?

Navigation

#1 by (6 votes)

7

I was studying here about security in PHP & MySQL, and I had a question: If I'm using prepared statments , is it still necessary to validate data entry?

For example, I have a field in the form:

<input type='text' name='campo'>

In the file script.php I get this variable like this:

$campo = empty($POST['campo'] ? NULL : $POST['campo']; // isso até é 
//uma validação né (ou não?), mas pra efeitos de segurança em prevenção      
//de PHP injection e SQL injection não sei se é efetivo

In the output file I use this variable, for example:

echo $campo;

Or:

if ($campo = 'sim') {
   return true;
   echo 'O campo é sim';
}

And at the end of the output file I call a file to write to the bank, using prepared statments , like this:

$sql = "INSERT INTO nometabela (campo) VALUES (?)";

$stmt = mysqli_prepare($mysqli, $sql);

mysqli_stmt_bind_param($stmt, 's', $campo);

mysqli_stmt_execute($stmt);
$stmt->close();
$mysqli->close();

In this way, I understand that SQL Injection is impossible because it will only accept string characters, so it would not be possible to include characters needed to change the query.

And let's also say that I use this field for some SELECT , is there any risk (even without being SQL Injection)?

In this answer for example it is suggested to validate with preg_match , but then I was in doubt whether with prepared statments this is no longer solved. Can the above code be considered secure? There's something wrong? Is there anything else I can do to ensure system integrity?

php prepared-statement mysqli

asked by anonymous 24.09.2015 / 09:11

1 answer

My CSS file is too large and it is difficult to keep readability How to map an image attribute by Fluent API?

score 6 · Accepted Answer

Risks

The risks are everywhere. For example, the code shown is assigning 'sim' to $campo . And sending something to print that will never be printed. It may seem silly to watch. But flaws occur because of silly things like that.

So, as shown in this question , validation should occur with any data coming from a source that you do not controls completely in the application.

Of course, if you use information in a query this way, the main validation is already done by the function used.

Invalid data

This does not prevent other invalid data from being used. As it is insertion then it is very likely that you want to check if the data is in proper state to enter the database. But in terms of safety there will be no direct risks. There may be indirect such as the person putting the password that she uses in a field that can be accessed by anyone publicly. But of course this is harder to prevent if the person wants to do the same.

Remember that information can be used for other things and there is a risk that your application will be vulnerable. There is not only SQL injection. One of the most common mistakes is to let the user enter a free-form file path.

Injection of content

There is also the injection of content that has nothing to do with SQL. In web applications it is common to try to place an HTML / JS code as an innocent die. When this data is accessed by someone, this code will be inserted in the normal page of your application and will execute something on the client that was not his intention. This creates a security problem for your users. This is also your responsibility. It gets worse if this information is used on the server to generate something that will execute.

DDOS

There is the denial-of-service attack that can be obtained by having the database work longer than it expects. This can occur because the data has not been validated. And it is not always easy to find a balance between whether data is valid or not in such situations. Validating a data to avoid robotization of requisitions is important as well. Almost no web application is prepared for this. Almost all of them are done in a very amateurish way. Luckily they are so irrelevant that they do not usually suffer these attacks.

Any attack is possible

I'll keep reminding of other attacks, but if I do not stop here the answer will continue to be edited and increased. Developing for web is much more complicated than people imagine.

Conclusion

Depending on the linked answer in the question here, you should restrict the accepted values as much as possible. Not only because of SQL injection .

Do not forget that there may be a bug in the function that handles the prepared statement . It may be rare, but if you validated before decreases the chances of an attack succeeding. I'm not saying to be so paranoid of mistrust that everything may fail, but it's something to ponder.

And the first example is a validation, bad, but it is. It does not help or disrupt SQL injection . And for me it worsens a situation in most cases. In general it is better to have an empty field than null. But if you know what you're doing, if you're sure it will always be checked if the variable is null before using, ok. It's not wrong, it's just a matter of style.

Positive test X negative test

But overall one of the biggest mistakes people make is to test the application to see if it's working. Tests are run to see if it is not working . And this is the most important. Testing everything What can go wrong is critical. Most do not value it and when it does, it does not know everything that needs to be tested. And I doubt there's anyone who knows everything that needs to be tested in complex applications. You just try to minimize, do not repeat the same mistakes, fix what was wrong as soon as you find a new problem.