real escape string and PDO

tags:

mysql
php

views:

answers:

+1 Q:

real escape string and PDO

I'm using PDO after migrating away from the mysql library. What do I use in place of the old real_escape_string function?

I need to escape single quotes so they will go into my database and I think there may be a better way to handle this without add(ing)slashes to all my strings. Can someone tell me what I should be using?

+1 A:

You should use PDO Prepare

From the link:

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.

SteD 2010-09-15 09:37:21

Thanks SteD. I read that a while back but I have since heard that PDO prepare will NOT prevent against SQL injection. I'm not sure what to believe anymore. Are you certain that prepare will stop injection attacks?

John 2010-09-15 09:39:33

@John that's first time I hear someone heard that PDO prepare will NOT prevent against SQL injection. Are you certain you really heard that? Is your source more reliable than official documentation? And note, there is always another way, different from asking someone - you can do a research yourself...

Col. Shrapnel 2010-09-15 09:42:51

PDO prevents SQL Injection. (It does not help prevent xss vunerabilities, but neither does mysql_real_escape)

nos 2010-09-15 09:43:44

Yes, that was why I wanted to ask for another alternative. I've read that in several places actually and remember thinking about how many sites I had written that will now be vulnerable. I'd love to have a definitive answer on this myself.

John 2010-09-15 09:44:50

@John you are doomed to be in constant doubts, if you plan to rely on someone's opinion, not your own knowledge. Answer yourself a question, `How does mysql_real_escape work?` `How does parametrized query work?` And you will be certain.

Col. Shrapnel 2010-09-15 09:46:49

John: Yes, if you use it correctly, there's a good explanation by Bill in this post --> http://stackoverflow.com/questions/1314521/how-safe-are-pdo-prepared-statements

SteD 2010-09-15 09:46:55

@nos: I agree about the XSS but I'm still concerned about prepare not protecting. Are you certain about that? If it is true that it will prevent the injection attacks, then I will just use that.

John 2010-09-15 09:47:51

@Col. Shrapnel - I have doubts and have not really looked in depth but figured that it couldn't hurt to ask others. I don't claim to be as good as the next joe.

John 2010-09-15 09:50:07

@SteD: Thanks, I'm reading it now.

John 2010-09-15 09:50:36

There is no way any database layer can protect you against XSS, because that's an issue of page-output-time escaping and not anything to do with the database. Whilst many misguided authors do try to HTML-escape at the same time as SQL-escaping or over input values, this is the wrong time to address it and will typically result in incomplete protection as well as other nasty bugs.

bobince 2010-09-15 09:56:32

+2 A:

Use prepared statements. Those keep the data and syntax apart, which removes the need for escaping mysql data. See e.g. this tutorial.

Piskvor 2010-09-15 09:37:59

Piskvor, thanks. I'm already using them just not where I'm getting the errors. I just was wondering if prepare really stops injection attacks. I've heard to the contrary and there seems to be a a lot of debate over it.

John 2010-09-15 09:43:06

@John: Yes, prepared statements will stop SQL injection attacks. (Of course, SQL injections are just one possible attack vector, so preventing them is not a magic "poof-your-website-is-now-fully-secure" dust)

Piskvor 2010-09-15 10:13:29

@John there is not a single debate. Actually it's only you who debate it.

Col. Shrapnel 2010-09-15 10:20:49

ansaurus

tags:

views:

answers:

real escape string and PDO

related questions