16

I have been researching into PDO's bindValue(). I know that preparing my SQL statements with PDO is keeping SQL injections from happening.

Code Example:

$stmt = $dbh->prepare('SELECT * FROM articles WHERE id = :id AND title = :title'); $stmt->bindValue(':id', PDO::PARAM_INT); $stmt->bindValue(':title', PDO::PARAM_STR); $stmt->execute();

By binding the ID as a number, and the Title was a string, we can limit the damage done when someone tries to do an SQL injection within the code.

Should we always bind our values with a PDO::PARAM_ so we can limit what can be pulled from the database in an SQL injection? Does this add more security with PDO when doing our bindValue()?

Improve this question
5
  • 1
    If you don't bind parameters used in a LIMIT as an int the query won't react like you think (I can't remember if it fails or if it ignores it). Commented May 7, 2014 at 7:27
  • 3
    ...as opposed to what? Other methods of binding that are not bindValue? Leaving our the PDO::PARAM_ argument? Or entirely forgoing the prepare API? Commented May 7, 2014 at 7:29
  • 2
    I'm pretty sure what OP means is the following: "Do I need to use PDO::PARAM_INT for a number or can I survive with the default PDO::PARAM_STR that's chosen if I don't select a param type? I realize it adds ''s around the numbers, but does it really matter?". Commented May 7, 2014 at 8:23
  • 2
    @h2ooooooo That is basically the question, but you worded it way better than I did. I don't understand why you would want an int to be a sting. Maybe I am too logical thinking. Commented May 7, 2014 at 8:30
  • 1
    @Fluorocarbon You might benefit from reading mysql - quote numbers or not?. That said, I always tend to use my own DB wrapper that checks the PHP type and then selects the correct PARAM type. (stackoverflow.com/a/22235348/247893) Commented May 7, 2014 at 8:33

4 Answers 4

Reset to default
10
+100

There are two questions in one. It is essential not to confuse them

  1. Should we always use a placeholder to represent a variable data in the query?
  2. Should we always use certain function in the application code to follow the above rule?
    Also, from the clarification in the comments under the opening post, the third question can be seen:
  3. Should we always use third parameter, or it's OK to let PDO bind all the parameters as strings by default?

1. For the first question the answer is absolutely and definitely - YES.

While for the second one, for sake of code sanity and DRYness -

2. Avoid manual binding when possible.

There are many ways to avoid manual binding. Some of them are:

  • ORM is an excellent solution for the simple CRUD operations and must have in a modern app. It will hide SQL from you completely, doing the binding behind the scenes:

    $user = User::model()->findByPk($id);

  • Query Builder is also the way to go, disguising SQL in some PHP operators but again hiding the binding behind the scenes: 

    $user = $db->select('*')->from('users')->where('id = ?', $id)->fetch();

  • some abstraction library may take care of the passed data by means of type-hinted-placeholders, hiding the actual binding again:

    $user = $db->getRow("SELECT * FROM users WHERE id =?i", $id);

  • if you are still using PHP in the last century ways, and have raw PDO all over the code - then you can pass your variables in execute(), still saving yourself a lot of typing:

    $stmt = $dbh->prepare('SELECT * FROM users WHERE id = ?'); $stmt->execute([$id]); $user = $stmt->fetch();

As of the third question - as long as you are binding numbers as strings (but not the opposite!) -

3. It's all right with mysql, to send almost every parameter as a string

as mysql will always convert your data to the proper type. The only case known to me, is a LIMIT clause where you cannot format number as a string - thus, the only related case is one when PDO is set in emulation mode and you have to pass a parameter in LIMIT clause. In all other cases you can omit third parameter, as well as explicit call to bindValue() without any problem.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

10

You should definitely use the prepare API and pass values separately from the query, as opposed to doing plain string interpolation (e.g. "SELECT * FROM foo WHERE bar = '$baz'" → bad).

For binding parameters, you have three options:

It doesn't really matter which of these you use, they're all equally secure. See these answers for some details about the differences:

When using bindParam or bindValue, passing the third PDO::PARAM_ argument type is optional. If you don't pass it, it defaults to binding the argument as string. That means you may end up with a query equivalent to ... WHERE foo = '42' instead of ... WHERE foo = 42. It depends on your database how it will handle this. MySQL will cast the string to a number automatically as needed, just as PHP does (e.g. in '42' + 1). Other databases may be more fussy about types.

Again, all options are equally safe. If you're trying to bind a string 'foo' using PDO::PARAM_INT, the string will be cast to an integer and accordingly bound as the value 0. There's no possibility for injection.

Improve this answer

Comments

2

Yes you should always bind params with prepared statement. It's more secure, and limit SQL injection. But this is not the only think you must do to query params: a correct type control is required, best if you map a row into an object and throw an exception in it if it has invalid data.

I hope I can be useful!

Improve this answer

Comments

1

Yes, binding is the way to go. Or parameterised queries which a more generalized term.

@Theo does a wonderful job explaining why parameterized queries is the way to go

You could also use stored procedures for additional security but is an over kill if you have one application to one database. Its good for multiple applications to one database to ensure consistency when handling data

Improve this answer

8 Comments

what is "additional" security and how stored procedures furnish it?
@YourCommonSense IDK why the down vote as nothing is wrong with my answer Only that there might be some information missing or a bit vauge which the comment section is for to mention... By additional security, you can enforce the type data being imputed into your database by performing checks on your data. SQL-Injection is not the only security issue in SQL...
I don't understand the word "additional" applied to security. If your code is secure - then no "additions" are ever needed. If not - then these "additions" won't guarantee you security either. I've seen too few people who indeed used stored procedures as a protection measure against injections. But I've seen a multitude who just tell each other to use them.
@YourCommonSense If you have multiple applications inserting data into a single database. How do you control the data? What if one application inserts malformed data which can cause other application to not work properly? If you use stored procedures, you have a centralized way to check the type of data going through/getting inserted to make sure it is not mallformed
Because without it your answer become just a link to other answer. Which is to different question, actually.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.