No, I don’t. Please ignore this person ︎, he has been trying to hijack my thread from the beginning with mis-characterizations and insinuations.
When I perform a database connection and query with MySQLi, it returns information about that connection and query immediately in a result array. Information such as last insert id, errors, thread id, affected rows, number of rows retrieved, number of fields in each row. I don’t have to retrieve the last insert id with $msyqli->insert_id or $pdo->lastInsertId(). All that information is already in the result returned by MySQLi. Information that is quite useful after interacting with the database. With PDO I have to specifically ask for each piece of that information back after each connection and query with commands like lastInertId(), rowCount(), columnCount(), etc.
Not a big deal, but the code is a bit cleaner, IMO, with MySQLi. But I’m using my own database class wrapped around PDO to do database interactions, so it’s not really an issue as I can just put those PDO commands in a method and return the same information to me after a query has been done.
I’m simply maintaining two PHP applications that are running a business, I’m not creating applications for sale or for other companies, so, my focus and development environment (LAMP) are fairly narrow. Which is why the biggest selling point of PDO (that it can interact with multiple databases) is moot for me.
I am in the process of transitioning these 15-year-old PHP applications to non-deprecated technologies and commands, so, that’s why initially MySQLi was attractive. That part would be a fairly easy transition. But as I’m also trying to use prepared statements, I figured PDO might be better for that simply because of the named variables, which seems more intuitive to me.
Speaking of prepared statements, I’m wondering what the advantage of using them is over, say, sanitizing and escaping all user-input data included in queries yourself, especially considering that prepared statements do add a bit of overhead to the database server. What do prepared statements offer that sanitizing and escaping don’t?
I don’t have to retrieve the last insert id with $msyqli->insert_id
This statement, so to say, is not true. This is the exact code which is giving you the insert id from mysql. So if you need insert id, you need $msyqli->insert_id, no more, no less. And you have to call it, this way or another.
Therefore, the difference boils down to the very braces I was talking above. There is NO difference other than syntax.
I can’t remember what advantage doing something like this is, but it seems like a good thing. Imagine you’re loading a csv file into a table:
$csvfile = fopen("myfile.csv", "r");
$que = "insert into mytable(name, email) values(:name, :email)";
$n = ""; // placeholders because you have to call
$e = ""; // bindParam() on variables that exist
$p = $db->prepare($que);
$p->bindParam(':name', $n);
$p->bindParam(':email', $e);
while ($row = fgetcsv($csvfile)) {
$n = $row[0];
$e = $row[1];
$p->execute();
}
So basically you can bind the variables to the query at one point, then just keep calling execute() every time the variable values change. I believe this reduces bandwidth usage as only the parameters are sent to the server each time. It’s a while since I’ve read up on this, though, but I’m sure someone will correct me if I’m wrong. Above is just an example, so I’ve left out any checking of the input values that you’d want to do.
I haven’t done enough with PHP to say how often you’d run into something like that, but the point is that prepared statements aren’t just about sanitising data.
With a prepared statement you set in stone exactly what the query will look like in code written by your hand only. It only lacks the actual data which will be provided on execution.
For example you are doing an insert for two columns.
"INSERT INTO MyTable (this, that) VALUES (:this, :that)"
So it is already decided, before a user can influence anything, that we are going to insert two values into the two named columns in the named table. Nothing more and nothing less.
So there is no way a user can manipulate the query to do something more, like: Insert this and that, then select all the email addresses, then drop the table (or whatever). Because it has already been established that we are doing a single query that inserts two values into the two named columns in the named table and nothing more.
Then you pass over the values on execute, and it happens.
No need to escape, but for a “belt & braces” approach you should still sanitise and validate all user input. But do still encode on output to a page from the database.
Not something you will use every time, but very useful and efficient. Prepared statements are reusable, you send it once and use it over and over any number of times, likely with different values.
For example, prepare a query once, then enter a foreach loop and use the same query for every element in an array.
Or prepare once, then have conditionals that determine the the data passed into the query.
Rumors and superstition? There’s nothing “superstitious” about what I have posted.
This however, is a “superstition”. It isn’t “a pain” to use prepared statements in mysqli_*. It’s actually pretty simply. Probably just as simple as PDO.
No examples? There are tons and tons of examples in that SO article I posted. You clearly didn’t go through them all. Just read all the comments in that article. I don’t need to repeat something when more than 1 person has already stated the reason.
Look, I am not pushing support for mysqli_*, but I will also not blatantly turn a blind eye to what others have said in that SO article. Here are my notes and pointers as to why I dislike both libraries based on my own testings and findings.
mysqli
Dislikes
People use it as if they were still using mysql_*. All the legacy codes still exist today even when plenty of hosts offer PHP 7.
Some functions from PDO don’t exist in mysqli_* so you have to write a little more code just to get the same results.
Likes
Simple and very easy to use.
Single line connection and you don’t have to mess around with the database to get any encoded characters to work.
PDO
Dislikes
Requires multiple lines of code just to get encoded characters to work and even then, you have to make changes to the database tables in order to get encoded characters to work.
Emulated prepared statements are always on by default when using PDO with MySQL.
Likes
Has some single line functions that mysqli_* doesn’t have such as ->fetchAll();
Ironically, you showed mysqli’s regular queries. You do know that it also has prepared statements correct? What you should of done was showed prepared statements along side prepared statements.
Look, PDO has regular queries too. “Let’s show everyone that so we can prove a point that is very pointless.”
$stmt = $mysqli->prepare("UPDATE articles SET title=?, text=? WHERE id=?");
$stmt->bind_param('ssi', $title, $text, $id);
$stmt->execute();
vs
$stmt = $pdo->prepare("UPDATE articles SET title=?, text=? WHERE id=?");
$stmt->execute([$title, $text, $id]);
Also, weren’t you the one who was pushing support for simplicity and “single line” codes? To create a well solid PDO connection, one must apply the right options to the connection when using PDO. This also isn’t “superstition” as you may have put many things you dislike. See for yourself.
$options = array(
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ, // Set fetch mode to object since associative arrays are default
PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING, // Set the error mode to error warning
PDO::ATTR_EMULATE_PREPARES => false, // Set emulated prepared statements off
PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8' // Set the character set to UTF8
);
$db = new PDO('mysql:host=localhost;dbname=' . $database, $username, $password, $options);
And even then, the above code won’t work for text emjois such as ( ͡° ͜ʖ ͡°). You have to actually go into the MySQL database and change the data type to something that works.
Oh look at mysqli_*. “Shows one line of code”.
$db = new mysqli($host, $username, $password, $database);
Would you look at that… mysqli_* doesn’t need a million lines to get encoded characters to display such as ( ͡° ͜ʖ ͡°).
And again. That “one line” of code though… Let’s push for it?
Anyways, enough with the trolling. I dislike both libraries, but if you must, learn both libraries. Again, this isn’t “superstition” if I always test it before commenting on something.
You should learn to read. And not to spam this forum with irrelevant rants.
My objection was concrete and addressed a very particular statement of yours: “some of your queries will break”, which I cited in my post. And which is a blatant lie, in the context of mysqli vs. PDO comparison. Remarkably, in your latest rant you didn’t address this issue with a single word.
Instead, you tried to ridicule my other post that answered a completely different question, “What is the advantage of the prepared statements over regular queries?”, for which it’s perfectly valid.
And the last part of your post is a darkest example of hypocrisy, as an array of parameters for PDO is deliberately optional, and one can connect to PDO with a single line as well. But in such a case the connection would be crippled, as crippled as mysqli from your one-liner. In effect, you are trying to cheat, calling a nice feature (ability to use configuration options) a disadvantage.
To get the same result from mysqli one would have to make several commands as well, such as mysqli_report(), mysqli_set_charset() or mysqli_options().
Remarkably, that the following statement is again a deliberate lie,
$db = new mysqli($host, $username, $password, $database);
Would you look at that… mysqli_* doesn’t need a million lines to get encoded characters to display such as ( ͡° ͜ʖ ͡°).
As the code provided doesn’t set the character set for the connection. To make mysqli properly encode your data, you have to make an extra call, whereas for PDO charset could be supplied in the DSN. Therefore, to make PDO work with extended charsets, you need the same one line:
$pdo = new PDO("mysql:host=$host;dbname=$db;charset=$charset", $username, $password);
whereas for mysqli you will need two
$db = new mysqli($host, $username, $password, $db);
$db->set_charset($charset);
Not that I take any care for the number of rows in the configuration file that have to be written once for ages, but I can’t stand a blatant lie and hypocrisy.
So you are either deliberately cheating or just have no idea on how to use mysqli properly. In either case you better refrain from posting your opinions in this topic.
I’m curious about the encoding issue you mentioned. I have my database and tables set to utf8_unicode_ci. Does PDO require me to specify the charset for every insert? I am presuming the insert will take on whatever charset the table is set to, as MySQLi appears to do.
Actually, setting charset through DSN is preferred.
However, as long as your database is mysql and encoding is utf-8 this rather clumsy ATTR_INIT_COMMAND stuff could be excused.
You know, I’ve come to the conclusion, and gone back to my original realization, that PDO is an extra layer I don’t really need. After all, both MySQLi and PDO are using the same exact MySQL database and I will never need to use anything but a MySQL database for this application and project. MySQLi provides a simple and direct interaction with my database and has all the features I need and want, so, I just don’t see the point of trying to shoehorn PDO into my application after this discussion.
Such a separation sometimes considered as the only security feature to prevent SQL injection, but the same degree of security can be achieved with non-prepared statements, if all the values are formatted correctly.
Which is why I asked above, “what do prepared statements offer that sanitizing and escaping don’t?” Now, that sentence is also followed by:
It should be noted that correct formatting is not the same as escaping and involves more logic than simple escaping. Thus, prepared statements are simply a more convenient and less error-prone approach to this element of database security.
I also read somewhere else on the PHP site that you still need to validate and sanitize your data before passing it on to a prepared statement anyway, so, it seems that prepared statements aren’t the holy grail in avoiding SQL-injection attacks some people make them out to be. Yes, they are more convenient and less error-prone than doing it yourself, but it’s not the only or whole solution.
Another interesting point made on that PHP page:
Using a prepared statement is not always the most efficient way of executing a statement. A prepared statement executed only once causes more client-server round-trips than a non-prepared statement. This is why the SELECT is not run as a prepared statement above.
So it seems that the primary purpose of prepared statements is to make queries that need to be run multiple times during the execution of a script more efficient and use fewer database resources. Using them for ALL queries, especially once-only queries, seems unwise as it just adds additional, unnecessary overhead. Usually when I need to run a query multiple times it’s to insert multiple sets of records into a table. And for that, the PHP site says:
Also, consider the use of the MySQL multi-INSERT SQL syntax for INSERTs. For the example, multi-INSERT requires less round-trips between the server and client than the prepared statement shown above.
For my purposes, I rarely run the same query more than once during the execution of a script. I also make sure any user-supplied data that’s included in a database query is validated, sanitized, escaped, and that it’s the proper type of data (integer, string) before including in my query.
That’s why I’m going to go with MySQLi and direct queries, and only use prepared statements when I need to run a query multiple times during the execution of a script that’s not an INSERT. And based on my research here and elsewhere, I think it’s the correct and most efficient way to continue.
Okay, after being heckled and a few private conversations and additional research, I’ve been convinced that, yes, prepared statements ARE necessary, despite what that page on the PHP site I referenced above says. <deep breath>
Now, what I don’t quite understand is whether prepared statements need to be used for ALL queries. For example, at the start of a script I load all user preferences once only. This is a “hidden” query that gets no user input.
SELECT * FROM prefs WHERE userid = 9999
The userid does comes from a session variable set after being logged in. Is that were the risk of potential SQL-injection lies, in the validated and sanitized session var that is inserted into the query?
If so, okay, I get it. But what about queries that are even more straightforward, like:
SELECT * FROM menu_options
Does this query need to be sent as a prepared statement? If so, how is SQL-injection possible for a query like this that is hard-coded and contains no variable or user input?
Please, no rude replies or sarcastic remarks, I’m just trying to learn here and have lots of questions and my thoughts are constantly evolving because this is all incredibly confusing with so many differing (and strong) opinions about MySQLi and PDO. Thanks.
What I do is use prepared statements if the query contains any user input or variable that could possibly be manipulated by a user.
I also use prepared statements if I want to reuse the query multiple times with different values.
With a hard coded query I see no need, it is what it is. Reuse is no use here and injection is not an issue here because there are no variables.
Some may say use them for any query with a variable, but I tend not to if I hard code the values myself, Eg.
It is true that the main reason for prepared statements is re-usability for server efficiency, but as per my explanation in post #27, you can see how it is a very effective guard against injection and should not be ignored for that purpose.
For a situation like that even though it’s coming from a session variable, if a prepared statement isn’t used, at some point you might change the source of the id value to user submitted data, you’d then opened a security hole. By using a prepared statement for any query that uses a supplied value (no matter the source of that value), that isn’t hard-coded you prevent any security holes from accidentally being opened
The PHP documentation is correct about prepared statements and all the quotes you posted here are also correct. Prepared statements are not necessary for security - instead, you can quote all values you insert into queries and you also have to remember to set the right charset at connection - and you will be fine. But for some reason, prepared statements have become a de-facto standard for security, in spite of the fact that security is more a by product rather than the goal of prepared statements. To most people this method looks cleaner and makes it easier not to overlook the necessary security measures.
Many years ago I made a fairly large PHP system and I didn’t use prepared statements but simply escaped all values - I even had my own convenience method in the database mysqli wrapper class for escaping that was easier to use than mysqli_escape_string. And this was all fine but when a new developer came (who was not that experienced in PHP and security) it took me quite a while to remind him time and time again to always escape values for SQL. It took him some time to finally understand and not to forget but I suppose it would have been easier if prepared statements had been the standard in that application.
Thank you for acknowledging that I wasn’t being a complete idiot in my #35 comment. I tend to trust what I read on php.net because it’s, you know, the source, the PHP bible, so to speak. This statement in particular really jumped out at me:
Using a prepared statement is not always the most efficient way of executing a statement. A prepared statement executed only once causes more client-server round-trips than a non-prepared statement. This is why the SELECT is not run as a prepared statement above.
And this one, too:
Such a separation is sometimes considered the only security feature to prevent SQL injection, but the same degree of security can be achieved with non-prepared statements, if all the values are formatted correctly.
So I’m wondering, what would that security entail without prepared statements? I presume it’s more than $cleanvar = addslashes(strip_tags($var)); I imagine with the filter_var() function it’s possible to thoroughly sanitize all data without using prepared statements. This is actually how I’d prefer to go, only using prepared statements to loop through a query with different data because I don’t like adding unnecessary additional overhead to the database server. I haven’t been able to find out what prepared statements actually do before substituting data with placeholders in a statement that prevents SQL-injection, so, I’m committed to just using them for nearly all queries and hope the database will handle the additional load.
If you were to re-do that large PHP app today, would you use prepared statements?
I think “forgetting” to run all user-input through a sanitizer function isn’t a good excuse for using prepared statements for every query, IMO, but I’m clearly in the minority. There’s a lot of things you should not forget when coding.
While I have your attention, what is your thought on MySQLi vs. PDO? I prefer named variables in PDO prepared statements, but otherwise like MySQLi as I will always be using a MySQL database for my applications.
Oh no - these are not the functions for SQL security and in fact they are not necessary, they have a different purpose. There’s only one escape function to be used for security against SQL injections: mysqli_real_escape_string. The other thing to do is set the correct character set when connecting because this escape function will use that info for its proper working. When you do these two things then you don’t need to use any other escaping or sanitizing functions - you can literally throw any garbage data into your SQL, however unsafe it may look, and you will be protected because mysqli_real_escape_string will make it safe. Of course, you still need to watch where you insert these values - they belong only to quoted strings in SQL queries.
Possibly. In my later projects I decided to see how using PDO and prepared statements would make the difference to my coding and I find the latter style a bit cleaner. To be precise - now most of the time I use Doctrine DBAL as the database access class, which brings a few convenience methods and allows to use all the power of PDO as well.
The downside of prepared statements is that it’s more difficult to debug queries. When you construct the whole query with data as a string then it is very easy to log problematic queries and then see exactly the queries that were run. With prepared statements it’s not so easy to log them because the statement and data are separate so you need to log them both and then rebuild them for debugging. DBAL partially solves the problem.
While mysqli is certainly up to the task I like PDO better. With mysqli I’ve always needed an additional class as a wrapper because mysqli alone is quite awkward for most basic needs - many lines of code to do a simple thing. While PDO is not perfect its interface is nicer and more usable on its own and its many fetch styles are really sweet and useful (have a look at all the PDO::FECH_* constants). And obviously, prepared statements are so much cleaner with PDO than mysqli.
Also, I find myself using MySQL, PostgreSQL and SQLite for various projects and having a single consistent PDO interface is very nice.
BTW, in PDO you can use emulated prepared statements and then there are no additional round trips to the database server because the preparing is done at the PDO level. You can use that option if you worry about performance. Also, PDO has the quote method equivalent to mysqli_real_escape_string for cases when you don’t want to bind values for a prepared statement.
Interestingly, when you read the PHP manual for PDO quote you will find that this is discouraged in favour of prepared statements - slightly contrary to what you read about mysqli. But there’s not really any fundamental difference - it looks like the PDO manual was written by a person who leans more towards prepared statements rather than escaping values. Coding styles change and this is reflected in the documentation.
All in all, forget all the supposed performance benefits of prepared statements that people tend to mention. It is true that the server can cache the query plan to some extent but the benefit in today’s databases is extremely minimal and to be able to notice it you’d have to run a prepared statement thousands of times - and this is discouraged, anyway, since most of the time you run a query once and you try to minimize their number as much as possible.
︎, he has been trying to hijack my thread from the beginning with mis-characterizations and insinuations.