PHP PDO PARAMS required if using stored procedures?

PHP
1

I’m new to PHP, but not programming. Have come from an ASP [classic] background. In brief, I’m using PHP 5.4, with FastCGI on IIS7 and SQL Server 2005 Express. I’ve learnt the fundamentals, and have spent quite some time looking into security.

I’m sanitising both GET and POST input data. My db connection strings are in a separate file placed outside the web root. I’m using PDO prepared statements [though I’ve heard query+quote perform faster] with named placeholders along with db stored procedures.

I’m trying to understand why I would need to use additional arguments within the bindParam function, particularly data type options “PDO::PARAM_STR, 12”.

What are the benefits of specifying the data type and length within the bindParam? Is it needed if I’m using stored procedures in which the data type and length is already specified?

Thanks!

2

The dataType is to help php assign the appropriate value type for your parameters. So if you are sending in an INT, if you don’t specify PDO::PARAM_INT, it will send your parameter as a PDO::PARAM_STR (the default).

It is also used to identify INOUT parameters (as shown here).

As for the length, that is required for OUT parameters, but optional for others. I can’t say for certain if the length is used in the sanitation of the parameter value or not, but I imagine it “could be used”. The php manual is not clear on this.

3

Thanks for your response. I’ve now added the additional PDO::PARAM argument. However, after adding the data length, I kept receiving an error that my sql parameters were missing an OUTPUT declaration. Eventually found out that if one specifies a data length within bindParam, then the “driver interprets it as an indication that you are binding an output (or in-out) parameter. For input-only parameters, there is no reason to have a length, since it is inferred from the actual value”. [Ref: http://social.msdn.microsoft.com/Forums/en-US/sqldriverforphp/thread/c7431849-8594-4da2-bef6-8702b72391cc]

I have another question regarding the use of PDO. I understand PDO does all the character escaping automatically and so have noticed characters such as ’ and " are saved as their html entity reference [i.e. ' and "]. Previously [in ASP] I used to simply perform a str replace " ’ " to " ‘’ ", which would save the apostrophe as ’ in the database. Is there a correct way of saving these and other special chars?

Many thanks.

4

You can use http://us2.php.net/manual/en/function.html-entity-decode.php

5

So how would you save the chars into the database? As an actual apostrophe or as the html entity? Am confused as to which is the correct way. Thanks.

6

I really don’t know. Depends on how you wish to use the data once it is in the database. For example, if you use SSRS, having the encoded value sucks because now you have to convert from the encoded value to its appropriate variant within your report.

You might be able to get around this by not using prepared statements, and instead use query() and [url=http://us2.php.net/manual/en/pdo.quote.php]quote()

7

My bad. I thought the use of prepared statements was converting a ’ to '

It was the FILTER_SANITIZE_STRING function performing that conversion before i passed the data to the prepared statement.

As a test, to see which chars are converted, i put these chars in a text field:

! " £ $ % ^ & * ( ) _ - + = { } ; : ’ @ # ~ . / ? | \

And this was the output after sanitisation:

! " £ $ % ^ & * ( ) _ - + = { } ; : ' @ # ~ . / ? | \

Only the single and double quote chars are escaped. I suspect this is to minimise/remove the risk of sql injection from user input?

Anyway, I really am confused as to the path i should take. Security is more important. So perhaps I should stick with html entities being saved in the database, and then html_entity_decode on screen output…?

OR

Do i need to do away with FILTER_SANITIZE_STRING type functions, and just rely on PDO and prepared statements to do the correct thing? [Assuming that it does].

8

You do not need FILTER_SANITIZE_STRING because PDO using prepared statements will take care of that for you to prevent a sql injection.

Granted you should use htmlentities when writting the database input back to the browser (echo htmlentities($row[‘columnName’]):wink: to help prevent XSS attacks

9

I’ve changed my code and now see apostrophes and double quotes being saved into the db as chars as opposed to html entities. Here’s a sample of my PDO code. Placeholders should protect me from sql injections, correct? [just seems to good to be true that PDO does it automatically!!]

$sql2 = $conn->prepare('EXEC update_table :userId, :firstName, :lastName, :company, :email, 1');
$sql2->bindParam(':userId', $_SESSION['userId'], PDO::PARAM_INT);
$sql2->bindParam(':firstName', $_POST['firstName'], PDO::PARAM_STR);
$sql2->bindParam(':lastName', $_POST['lastName'], PDO::PARAM_STR);
$sql2->bindParam(':company', $_POST['company'], PDO::PARAM_STR);
$sql2->bindParam(':email', $_POST['email'], PDO::PARAM_STR);
$sql2->execute();

I’m using htmlentities() to output any of the POST data to the browser. Is there any function I should be using when outputting POST data to an email?

Thanks.

10

Correct. Using prepared statements automatically protects against SQL injections.

[/QUOTE]I’m using htmlentities() to output any of the POST data to the browser. Is there any function I should be using when outputting POST data to an email?[/QUOTE]

Is your email Plain Text or HTML formatted?

If it is Plain Text, you are just fine using the data as it exists (no need for htmlentities).
If it is HTML formatted, you need to use htmlentities.

11

It was plain text, but i’ve changed to html format now. So added htmlentities.

Trying to think of scenarios of when one would use functions such as FILTER_SANITISE? I am using FILTER_VALIDATE_INT for validating GET data. But if i’m using PDO prepared statements which protects from injections and saves the data as raw, then outputting the data via htmlentities, seems I have no use for FILTER_SANITISING functions.

I guess it depends on the data input and its purpose. Example, an email address should really go through a FILTER_SANITIZE_EMAIL function. Are there any other scenarios as to when you would use FILTER functions?

12

Using FILTER_SANITIZING/VALIDATING functions can still be useful. As they will help you determine if you should not run a SQL command because the data doesn’t match up with what is you expected (you should always validate your data prior to storing it).

You can also use FILTER_SANITIZING functions to remove unwanted characters from a string, so if you are expecting a number, you can have it remove all alpha characters (FILTER_SANITIZE_NUMBER_INT). In most cases, it helps with validation of your data.

When using PDO and filter_vars, I typically filter any expected strings with filter_vars($mystring, FILTER_SANITIZE_STRING, FILTER_FLAG_NO_ENCODE_QUOTES);, so it doesn’t encode the quotes and apostrophes (I then use htmlentities for displaying this string back to the user).

13

What about for an email field? Do you use FILTER_VALIDATE_EMAIL/FILTER_SANITIZE_EMAIL? I have a client side JavaScript function in place which checks for a valid email address. Thinking I could use FILTER_SANITIZE_EMAIL just to ensure there’s no dodgy chars…

14

Yes, you shouldn’t trust JavaScript validation

15

Thanks for your help. Finished my first php mini project. Yippy!! Definitely prefer it than ASP [classic].