Replacing mysql_* functions with PDO and prepared statements

by

Thanks for the interesting question. Here you go:

It escapes dangerous characters,

Your concept is utterly wrong.
In fact “dangerous characters” is a myth, there are none.
And mysql_real_escape_string escaping but merely a string delimiters. From this definition you can conclude it’s limitations – it works only for strings.

however, it is still vulnerable to other attacks which can contain safe characters but may be harmful to either displaying data or in some cases, modifying or deleting data maliciously.

You’re mixing here everything.
Speaking of database,

  • for the strings it is NOT vulnerable. As long as your strings being quoted and escaped, they cannot “modify or delete data maliciously”.*
  • for the other data typedata – yes, it’s useless. But not because it is somewhat “unsafe” but just because of improper use.

As for the displaying data, I suppose it is offtopic in the PDO related question, as PDO has nothing to do with displaying data either.

escaping user input

^^^ Another delusion to be noted!

  • a user input has absolutely nothing to do with escaping. As you can learn from the former definition, you have to escape strings, not whatever “user input”. So, again:

    • you have escape strings, no matter of their source
    • it is useless to escape other types of data, no matter of the source.

Got the point?
Now, I hope you understand the limitations of escaping as well as the “dangerous characters” misconception.

It is to my understanding that using PDO/prepared statements is a much safer

Not really.
In fact, there are four different query parts which we can add to it dynamically:

  • a string
  • a number
  • an identifier
  • a syntax keyword.

so, you can see that escaping covers only one issue. (but of course, if you treat numbers as strings (putting them in quotes), when applicable, you can make them safe as well)

while prepared statements cover – ugh – whole 2 isues! A big deal 😉

For the other 2 issues see my earlier answer, In PHP when submitting strings to the database should I take care of illegal characters using htmlspecialchars() or use a regular expression?

Now, function names are different so no longer will my mysql_query, mysql_fetch_array, mysql_num_rows etc work.

That is another, grave delusion of PHP users, a natural disaster, a catastrophe:

Even when utilizing old mysql driver, one should never use bare API functions in their code! One have to put them in some library function for the everyday usage! (Not as a some magic rite but just to make the code shorter, less repetitive, error-proof, more consistent and readable).

The same goes for the PDO as well!

Now on with your question again.

but by using them does this eliminate the need to use something like mysql_real_escape_string?

YES.

But I think this is roughly the idea of what should be done to fetch a user from a database

Not to fetch, but to add a whatever data to the query!

you have to given a length after PDO:PARAM_STR if I’m not mistaken

You can, but you don’t have to.

Now, is this all safe?

In terms of database safety there are just no weak spots in this code. Nothing to secure here.

for the displaying security – just search this site for the XSS keyword.

Hope I shed some light on the matter.

BTW, for the long inserts you can make some use of the function I wrote someday, Insert/update helper function using PDO

However, I am not using prepared statements at the moment, as I prefer my home-brewed placeholders over them, utilizing a library I mentioned above. So, to counter the code posted by the riha below, it would be as short as these 2 lines:

$sql="SELECT * FROM `users` WHERE `name`=?s AND `type`=?s AND `active`=?i";
$data = $db->getRow($sql,$_GET['name'],'admin',1);

But of course you can have the same code using prepared statements as well.

* (yes I am aware of the Schiflett's scaring tales)

More Related Contents:

  1. PHP – Using PDO with IN clause array
  2. PDOException SQLSTATE[HY000] [2002] No such file or directory
  3. PDO get the last ID inserted
  4. PDO mysql: How to know if insert was successful
  5. MySQL check if a table exists without throwing an exception
  6. pdo prepared statements with wildcards
  7. Can I use a PDO prepared statement to bind an identifier (a table or field name) or a syntax keyword?
  8. Fetching single row, single column with PDO
  9. Insert multiple rows with PDO prepared statements
  10. PDOstatement (MySQL): inserting value 0 into a bit(1) field results in 1 written in table
  11. PDO::PARAM for type decimal?
  12. Calling stored procedure with Out parameter using PDO
  13. When *not* to use prepared statements?
  14. PDO error: SQLSTATE[HY000]: General error: 2031
  15. Using LIKE in bindParam for a MySQL PDO Query [duplicate]
  16. Use of PDO in classes [duplicate]
  17. Error while using PDO prepared statements and LIMIT in query [duplicate]
  18. I am seeing this error on submitting form Error: query was empty [closed]
  19. How do I set ORDER BY params using prepared PDO statement?
  20. Show values from a MySQL database table inside a HTML table on a webpage [duplicate]
  21. How can I store and retrieve images from a MySQL database using PHP?
  22. mysqli_stmt::bind_param(): Number of elements in type definition string doesn’t match number of bind variables
  23. Mechanisms for tracking DB schema changes [closed]
  24. PDO Connection Test
  25. PHP PDO::bindParam() data types.. how does it work?
  26. PHP PDO and MySQLi [duplicate]
  27. PDO fetch one column from table into 1-dimensional array
  28. How to store an array into mysql?
  29. PHP – Secure member-only pages with a login system
  30. Using Prepared Statement, how I return the id of the inserted row?

Leave a Comment