Real escape string and PDO

Question

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. What should I be using?

A bit late to the party, but you could use PDO::quote() if the prepare() method is not an option (for example, when you have build your own model) — Gerben Jacobs, May 09 '13 at 19:42

score 64 · Accepted Answer · answered Sep 15 '10 at 09:37

64

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.

answered Sep 15 '10 at 09:37

SteD

13,909
12
65
76

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 Sep 15 '10 at 09:39
4

PDO prevents SQL Injection. (It does not help prevent xss vunerabilities, but neither does mysql_real_escape) – nos Sep 15 '10 at 09:43
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 Sep 15 '10 at 09:44
4

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 Sep 15 '10 at 09:46
@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 Sep 15 '10 at 09:47
@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 Sep 15 '10 at 09:50
1

@SteD: Thanks, I'm reading it now. – John Sep 15 '10 at 09:50
8

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 Sep 15 '10 at 09:56
If this answer is correct then why is my PDO prepare not properly escaping single quotes from strings contained in parameters? – Adam Copley Dec 21 '15 at 16:12
"PDO prepare will NOT prevent against SQL injection" - It will protect against injection via constant values (e.g. variable assignments), but it won't protect against injection elsewhere, e.g. in identifiers (table/column names). If you use a table name that comes from user input then you need to take care to avoid an SQL injection via that vector. – HappyDog Aug 08 '17 at 15:23
For an in-depth explanation of how prepared statements protect against SQL injection, read: https://stackoverflow.com/a/12202218. – Tiffany Jul 16 '18 at 12:59

score 57 · Answer 2 · edited Aug 28 '17 at 21:20

57

PDO offers an alternative designed to replace mysql_escape_string() with the PDO::quote() method.

Here is an excerpt from the PHP website:

<?php
    $conn = new PDO('sqlite:/home/lynn/music.sql3');

    /* Simple string */
    $string = 'Nice';
    print "Unquoted string: $string\n";
    print "Quoted string: " . $conn->quote($string) . "\n";
?>

The above code will output:

Unquoted string: Nice
Quoted string: 'Nice'

edited Aug 28 '17 at 21:20

Peter Mortensen

30,738
21
105
131

answered Nov 29 '13 at 23:04

PowerAktar

2,341
1
21
17

14

I am currently in the process of migrating old code to use PDO and while the solution is a good one, people need to be aware of the fact that it will place quotes around the string. So if people are building out queries that already have quotes like `"SELECT * FROM users WHERE username = '".$conn->quote($username)."'";`, the queries will inevitably fail. – user1669496 Jun 11 '14 at 18:05
18

The accepted answer might be the recommended way, and the best practice, but this answer is the correct answer to the actual question. sometimes you actually need the old mysql_escape_string function, for instance if you are building a file to be executed later. you cant do that with a prepared statement. – Brent Larsen Mar 20 '15 at 01:32
Also prepared statements might impose a performance penalty if executed only once. – Eli Algranti Nov 12 '20 at 05:56

score 7 · Answer 3 · edited Aug 28 '17 at 21:19

7

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

edited Aug 28 '17 at 21:19

Peter Mortensen

30,738
21
105
131

answered Sep 15 '10 at 09:37

Piskvor left the building

91,498
46
177
222

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 Sep 15 '10 at 09:43
1

@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 left the building Sep 15 '10 at 10:13
1

@John there is not a single debate. Actually it's only you who debate it. – Your Common Sense Sep 15 '10 at 10:20
@Javi Ps: Yes. And...? – Piskvor left the building Jan 05 '16 at 11:48
Ah OK, just thought there was something more you wanted to add to it. Good point with the links to documentation , thanks. – Piskvor left the building Jan 05 '16 at 11:55
1

Prepared statements don't protect against all types of SQL injection, e.g. if you use dynamic table names. See my comment on the accepted answer for more details. – HappyDog Aug 08 '17 at 15:25
Of course they are not foolproof; with sufficient effort, you can shoot yourself in the foot using anything. However, they do remove the most common error types/attack vectors. – Piskvor left the building Aug 08 '17 at 16:16
it helps in some cases to be sure, but if your trying to insert or update data and the column and or table names come from variables. then it doesn't help at all. because the only time it'll let you include column names as data is in select statements. so if your getting table names from a variable or your doing an insert or update and your getting the column names from a variable(s) then you still need to escape the data. – Kit Ramos Apr 22 '19 at 15:45
@KitRamos: I would say that while there are various special cases, prepared statements help _in the most common cases_. Sure, if you're doing something advanced, this might not be the tool fit for the job; but I would guess that 90% of SQL statements as written (and 99.9% by volume as executed) _does_ fall into the select/insert/update category where prepared queries are the right tool for the job. (From what I've seen in code - note that the question is from 2010 - dynamic column names are either a.highly specific metacode (database management), or b.disarchitected code (denormalization)) – Piskvor left the building Apr 23 '19 at 15:27

Real escape string and PDO

3 Answers3

Linked

Related