Grow your CSS skills. Land your dream job.

PHP – PDO database use

  • # October 18, 2012 at 4:06 pm

    I am still using good ol’ mysql in order to access databases. It’s served me well, and I can use it pretty well without issue.

    I know it’s depreciated so I should be moving on, so I read up about PDO and mysqli.
    [PDO DB Access Tutorial](http://net.tutsplus.com/tutorials/php/php-database-access-are-you-doing-it-correctly/ “PDO DB Access Tutorial”)

    It stresses prepared statements. This brings me to my question:
    I use HTMLPurifier, so what does preparing statements do that I will benefit from? Can I dump HTMLPurifier?

    Thanks for any guidance!

    # October 18, 2012 at 7:18 pm

    The two are completely unrelated. HTMLPurifier does not address database security at all: it addresses vulnerabilities in your HTML, such as cross-site scripting attacks.

    Prepared statements, on the other hand, address the issue of escaping data you send to your database. Now, we’re talking about stopping SQL injection and protection from plain ol’ SQL errors. It’s also more efficient in cases where you use the same query more than once in your script, and mysqli/PDO offers a whole slew of other benefits (transactions, lazy connections, stored procedures, multiple statements, and so forth).

    To answer your question, **yes**, you should read up and start using mysqli or PDO in your scripts.

    **No**, this doesn’t mean you can “dump” HTMLPurifier, since it serves a different purpose entirely.

    # October 19, 2012 at 9:11 am

    awesome possum, thx for the reply!

    # October 19, 2012 at 9:51 pm

    no prob

    # October 24, 2012 at 11:18 am

    When doing database work, I have a quick question. Is it bad form to open the connection once at the top of the page (say before actually loading any content), and then using this connection throughout the script?

    Or is it a better idea, for some reason, to open the connection for every query or group of queries.

    My thought would be to use the same connection throughout. The reason I ask is because many tutorials show opening a connection for each statement, but that is probably just to show code-completeness I assume.

    # October 24, 2012 at 12:21 pm

    That’s a very good question. I tend to open the connection right before I begin my first query, then close it after the last one. I figured opening a new connection for each operation would increase server processes, but I’ve never actually verified if that would be the case.

    # October 24, 2012 at 4:19 pm

    I’ve spoken to a few guys who code backend apps and they all have said they don’t bother closing anything and have never run into problems.

    I just started using PDO, and I set my connection object to NULL when I destroy the class I use to connect to the db. I’ve read elsewhere online that it’s how you deal with PDO connection-closing. I could be ignorant and wrong!

    # October 25, 2012 at 12:08 am

    Yes, you should reuse a single DB connection throughout your script in most cases, but you should also avoid opening it until you’re sure you’re going to use it.

    There are cases where you’re going to use it “no matter what,” but there are also many times when you might not.

    (An example might be a login form – your first thought might be “of course, I’ll always need a connection,” but what happens if the user forgets to enter their username? You skip the query and give them an error message. Wasted connection.)

    Regarding closing [PDO connections](http://php.net/manual/en/pdo.connections.php), yes, ` = null` is how to do it.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic.

*May or may not contain any actual "CSS" or "Tricks".