Dormlich could likely confirm. Given that execute with an array treats everything as PARAM_STR, I would expect that it implicitly escapes the string identically to providing a string in a bind. Unfortunately, the API is not clear on this, although does suggest that you must EITHER pass the array to execute OR bind, so I would infer from that it is identical to bindParam.
Dormlich could likely confirm. Given that execute with an array treats everything as PARAM_STR, I would expect that it implicitly escapes the string identically to providing a string in a bind. Unfortunately, the API is not clear on this, although does suggest that you must EITHER pass the array to execute OR bind, so I would infer from that it is identical to bindParam.
Thanks. It was that vagueness that brought me here. I realize how I could have tested with an injection, but given the only sql server's I have to work with atm are clients', I'd rather not risk anything.
Thanks. It was that vagueness that brought me here. I realize how I could have tested with an injection, but given the only sql server's I have to work with atm are clients', I'd rather not risk anything.
I wasn't sure on bumping rules, so decided to leave it.
I don't have a lot of PDO experience since I typically write using the native MySQL[i] or SQLServer drivers, and PDO came after when I'd need to use it. So I dump everything through my own abstraction layer and extend to make use of the native libraries instead of using PDO. When I converted from procedural to OO, I probably should have lay weight to the PDO, but that was still early 5.0 version and I typically avoid both pecl and pear extensions when I don't need them. PDO was not native in PHP until 5.1.
dormlich appears to exclusively use PDO, so I'm sure he can help you with any questions regarding PDO.
Regardless of the "protection" built into such calls you should always validate the data if it was input by a person or sanitize it if it was obtained from elsewhere.
Regardless of the "protection" built into such calls you should always validate the data if it was input by a person or sanitize it if it was obtained from elsewhere.
While I can understand validating, eg, making sure the data is the type I expect, isn't the point of PDO to eliminate the need to sanitize? If not, then whats the point? Its more complicated, and more annoying, to use then straight queries.
Calling PDO::prepare() and PDOStatement::execute() for statements that will be issued multiple times with different parameter values optimizes the performance of your application by allowing the driver to negotiate client and/or server side caching of the query plan and meta information, and helps to prevent SQL injection attacks by eliminating the need to manually quote the parameters.
I saw that line as well kbluhm, but I wasn't sure if that means that execute actually does the sanitizing or not, and if so, as what (I assume string by default).
bind and execute don't do any sanitizing. All using prepare/bind does is keeps the data separate from the SQL so as to prevent SQL injection. It doesn't do anything to check that the data doesn't contain garbage. At best you will have the database call crash if the data is garbage (eg. trying to put text into a numeric field) but for most cases the garbage will just be processed as if it were valid (eg inserting a number into a name field that you didn't validate or sanitize to make sure it at least looks like a name).
So far, I have been using bindParam/bindValue. I was just trying to get a better understanding of how PDO works to better utilize it.
And felgall, perhaps I don't understand what sanitize means, but isn't sanitizing data the process of making sure you escape/process the data to prevent malicious queries? And isn't that different from validating? As I said above, I do validate, but if PDO isn't meant to sanitize, I don't see what purpose it has. In fact, kbluhm copied from the PHP manual where it says PDO does the work so you don't need to manually quote, which is sanitizing, lest I am mistaken.
And by way way felgall, if you try to put a word into a numeric field which you tell PDO should be numeric, it will turn it to 0 (same as intval). And if you try to insert words into a numeric field and don't tell PDO it should be numeric, it still converts it to 0. I even tried doing a malicious insert and it still just changed it to a 0 on insert.
I don't mean to be argumentative, but I feel like I'm being advice/answers contradictory to what I got here just a few months back when I first asked about best methods to prevent injection.
So please, correct me if I'm wrong. Isn't sanitizing the process to prevent malicious attacks (adding slashes, etc), and isn't validating the process of making sure the data you are receiving is in the format you want it in? And if PDO doesn't sanitize, whats the point in using it? Obviously its a lot more then just typecasting, because it so far seems pretty smart about figuring out what kind of data to expect, then making sure the data I give it is of that type, or converting it to that type.
You are getting sanitizing and escaping mixed up. One is done on input and the other on output and their purposes are completely different.
Sanitizing is stripping out characters that are invalid for the particular type of data it is supposed to be whereas validating means actually rejecting the entire field if it contains characters that are invalid. They are very closely related in that after performing either the resulting data is reasonable. See http://au.php.net/manual/en/filter.filters.sanitize.php for a selection of sanitizing filters that are built into PHP.
Both validating and sanitizing are INPUT functions that are performed to make sure that the data being processed makes sense.
Escaping is an output function that you perform on valid data in order to prevent that data being misinterpreted as a command. Using prepare statements in SQL keeps the data separate from the SQL and so avoids the need to escape the data in that instance. It does not have any effect on your input processing as sending data to SQL is an output function. Processing anything returned from the call is then an input function and sanitization should be considered as it prevents invalid data that somehow did manage to get into the database from doing any further harm in subsequent processing.
mysql_real_escape_string() and html_specialchars() are both output escaping functions intended to take valid data and to escape anything in that valid data that might be misinterpreted as SQL or HTML respectively.
A lot of people get their input and output processing mixed up as most of the examples they see when learning are small and often omit the input functions as provided that the data is valid no input functions to validate and sanitize are required whereas the output functions to escape the data are still required where the data is being jumbled into the command. In any real world program the escaping and sanitizing ensure that the data you are processing is reasonable - since most attempts to do harm involve data that would be considered invalid those attempts get blocked as a side effect of validation. If the validation or sanitizing wasn't there then keeping the data separate (eg. prepare/bind) or escaping the data will prevent the attack having the effect the attacker wants but it will still allow junk to be loaded into your database. The input processing to validate or sanitize the data prevents the junk getting that far.
Before you post, read our: 
