Is using Prepared statements and bound values enough to avoid SQL Injection with PDO?

Asked

Viewed 226 times

1

I wonder if it’s safe to search the database that way:

$c = $conn->prepare("SELECT * FROM tb WHERE coisa = :post");
$c->bindValue(':post', $_POST['login']);
$c->execute();
var_dump($c->fetch());

Is it safe for me to do it this way, or do I have to validate before? To avoid any kind of attack?

php pdo
  • 1

    In the title you mention SQL Injection, but in the end you talk about any type of attack. This code solves SQL Injection, but does not solve, for example, XSS injections. In this case you would need to filter the same values.

  • So @bfavaretto, after you talked about XSS, I researched it, one of the information I had, is that with the TAGS: strip_tags, addslashes e htmlspecialchars can solve this problem. Is it fact or not, what is the best way to avoid XSS with php? another question is, which global variables should I use to avoid this: ex: GET, POST, SESSION, COOKIE, SERVER ... because from what I read and from what I know, the user agent ($_SERVER), I can modify this header easily, and generally send it directly to the database... it is possible to modify the global variable of the IP as well?

2 answers

1

About SQL Injection

The preliminary defenses:

  • Option # 1: Use of Prepared Statements (parameterized queries)

The use of Prepared Statements with variable link (parameterized queries) is how all developers should be taught to write database queries. They are simple to write, and easier to understand than dynamic queries. Parameterized queries force the developer to first define all the SQL code, and then pass each parameter to the query later. This encoding style allows the database to distinguish code and data, regardless of what the user input is provides. Prepared Statements ensure that an attacker is not able to change the intent of a query, even if the SQL commands are inserted by an attacker. In the example , if an attacker tries to enter the tone userid "or" 1, the parameterized query 1 '=' would not be vulnerable and instead search for a username that literally accompanies all the string 'or' 1 '=' 1.

Specific recommendations for each language:

  • Java EE - use Preparedstatement() with bind variables
  • .NET - use parameterized queries like Sqlcommand() or Oledbcommand() with bind variables
  • PHP - use PDO with strongly typed parameterized queries (using bindParam())
  • Hibernate - use createQuery() with bind variables (called named Parameters in Hibernate) Sqlite - use sqlite3_prepare() to create a statement Object

There are other ways/complements to prevent SQL Injection, Prepared Statements is one of the main ones. I saw that you are interested in knowing about other vulnerabilities, including other variants of SQL attack like blind sql Injection. I don’t know if you know this, but there is a community organization that has a focus on security, especially the web and they make a top 10 of the main web vulnerabilities, outside that it has several other security related contents, even facing PHP.

Complementing: In addition, validate the inputs and outputs of your application, leaving the use of necessary characters.

OWASP

TOP 10 - 2013

SQL Injection

Reinforcement to you know this site, mainly to learn about the other ways to prevent an SQL attack, content was taken from there.

  • 2

    If it’s PHP with Mysql, better still use the native driver via mysqli than the PDO, to make native Binding. Remembering that Prepared is only better than sanitization when it is native (otherwise, as PDO does by default, at most "draw"). PDO with Emulated prepares is unsafe? What’s the difference?.

  • Native binding I did not know, at least now I will know. About sanitization, I preferred to leave it out for him to research further, on the OWASP website or elsewhere it is highly recommended to filter the data along with the use of Prepared Statements, however you should already know. @Bacco

  • 2

    the important thing is to understand that PDO simulates queries, it makes a "Binding" by assembling the client side query and sanitizing. This part you mentioned in yellow is important, but only occurs when Binding is done on the server side (as in mysqli, the data is transmitted separately from the query). I also think that sanitization is outside the scope of the question, I commented by comparison. By the way, at this point they have improved recently, when DB supports, now has to do native in PDO, but the default is not this. I only give the alert in these cases so no one buy cat by hare :)

0

Yes, using bindValue already avoids SQL Injection attempts.

  • 2

    This does not provide an answer to the question. To criticize or request clarification from an author, leave a comment below its publication.

  • 4

    @Brunoaugusto Do not answer? Of course you can put more details, but the essence of the answer is this same.

  • Sorry, at first I would like to get as main information SQL Injection, but Uq want to open a range for other types of attacks, as mentioned above XSS... If you have information about another type of attack that I may suffer using global variables in that way... and how not to let it happen. Just remembering that there was an issue in the title...

  • 1

    @abcd I left an answer the way the question came :) Better questions generate better answers. If you want to deal with xss Injection for example, ask a question on the subject, objective but with content and examples that surely the staff will give you good reporters.

  • Okay, I’ll do that.

Browser other questions tagged php pdo

You are not signed in. Login or sign up in order to post.