Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Regular Coder
    Join Date
    Sep 2011
    Posts
    310
    Thanks
    23
    Thanked 0 Times in 0 Posts

    Mysql_real_escape_string and PDO statements

    I hear many times some people keep saying to use PDO statement to prevent SQL injections. I still use mysql_real_escape_string against sql injection and htmlentities or strip tags for XSS to protect forms etc.

    So, why PDO statements? MySQL_real_escape_string is not good anymore? Does PDO statement provides better security than mysql_real_escape_string? I work with Procedural only just to enable people fill some short details to store in database, then retrieve them and display on pages.

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,978
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    Mysql library itself is old. Really old; API indicates that it is discouraged to use it old.
    I assume what you actually mean is prepared statements; PDO is an abstraction layer which happens to support prepared statements. MySQLi, OCI and SQLServer all support prepared statements as well.
    Prepared statements provide much better SQL injection prevent-ability. As in, 0% chance of injection. Prepared statements precompile the statement structure separate from the data so it is impossible for the data to corrupt the SQL itself with input data bound by variable. Its also a dream in batch processing.

  • #3
    Regular Coder
    Join Date
    Sep 2011
    Posts
    310
    Thanks
    23
    Thanked 0 Times in 0 Posts
    And can we add our traditional htmlentities or strip tags in PDO statements to protect against XSS?

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,978
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    Not in the statements, but you can do whatever you want to the variable first.

  • #5
    Regular Coder
    Join Date
    Sep 2011
    Posts
    310
    Thanks
    23
    Thanked 0 Times in 0 Posts
    Ok thank. I will consider it now. Hope I find a good tutorial on this. It is very rare you find PHP OO tutorials on books, videos etc... Most of them are difficult to follow.

  • #6
    Regular Coder
    Join Date
    Nov 2012
    Posts
    109
    Thanks
    6
    Thanked 12 Times in 12 Posts
    Quote Originally Posted by angelali View Post
    Ok thank. I will consider it now. Hope I find a good tutorial on this. It is very rare you find PHP OO tutorials on books, videos etc... Most of them are difficult to follow.
    If you do find a good tutorial on prepared statements, please let me know. I'm using the straight-forward MySQLi queries still.

  • #7
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,978
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    MySQLi can be used in a procedural fashion or an OO fashion.
    See the API documentation for an example of each: http://ca3.php.net/manual/en/mysqli-stmt.prepare.php
    MySQLi base also has a prepare method which would be more or less the same as going directly through the MySQLiStatement class.

  • #8
    Regular Coder
    Join Date
    Sep 2011
    Posts
    310
    Thanks
    23
    Thanked 0 Times in 0 Posts
    So PDO and Mysqlli are different? If yes, which one is better?

  • #9
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,978
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    Quote Originally Posted by angelali View Post
    So PDO and Mysqlli are different? If yes, which one is better?
    I suppose that depends on your definition. MySQLi is a library built for MySQL, like OCI is for oracle or SQLSRV for SQLServer. PDO is an abstraction layer using drivers for the DB in question, although it doesn't actually adhere to this abstraction mechanism entirely (ie: there is no way to tell it to issue a LIMIT beyond what is available in the SQL Syntax, so therefore it is not a full abstraction layer since its incapable of applying the logic to different dbms'). Rather, its simply a common entry point for typically used functionality for SQL dbms'.
    Theoretically, specific packages built for specific dbs should perform better overall than an abstraction layer would. But an abstraction layer has the benefit of being abstract, so I guess it depends on if you are looking for performance over swap-ability. If you intend to use MySQL proprietary syntax like group_concat or LIMIT, then you may as well use the MySQLi for the gain.

  • Users who have thanked Fou-Lu for this post:

    Thyrosis (11-30-2012)

  • #10
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,453
    Thanks
    0
    Thanked 632 Times in 622 Posts
    Quote Originally Posted by angelali View Post
    And can we add our traditional htmlentities or strip tags in PDO statements to protect against XSS?
    You should use htmlentities when writing data out into HTML where the data can legitimately contain characters that might otherwise be misinterpreted as being a part of the HTML rather than as a part of the content.

    You should use striptags where you want to convert HTML to plain text.

    Neither has anything to do with databases.

    Vakidating your data properly when it is first received from the user is the best way to prevent against XSS and also protects against your database getting filled with meaningless junk. If you validate your data properly then there is no possibility of XSS existing - it only happens because sites allow garbage to be entered in forms and don't validate it.

    How many people do you know whose name or address looks like an SQL command?
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #11
    Regular Coder
    Join Date
    Sep 2011
    Posts
    310
    Thanks
    23
    Thanked 0 Times in 0 Posts
    I have never said strip_tags and htmlentities are used for database! I said I used them against XSS. Read again.

  • #12
    Regular Coder
    Join Date
    Nov 2012
    Posts
    109
    Thanks
    6
    Thanked 12 Times in 12 Posts
    Quote Originally Posted by angelali View Post
    I have never said strip_tags and htmlentities are used for database! I said I used them against XSS. Read again.
    (reading again...)

    Quote Originally Posted by angelali
    And can we add our traditional htmlentities or strip tags in PDO statements to protect against XSS?
    Yes you did...

    By the way, XSS is client side by the way, and not related to SQL injection (correct me if I'm wrong please).

    Quote Originally Posted by wikipedia
    Cross-site scripting (XSS) is a type of computer security vulnerability typically found in Web applications. Due to breaches of browser security, XSS enables attackers to inject client-side script into Web pages viewed by other users.

  • #13
    Regular Coder
    Join Date
    Sep 2011
    Posts
    310
    Thanks
    23
    Thanked 0 Times in 0 Posts
    Yes as in Procedural we often protect textbox etc in variable, I wanted to know if in PDO also the same. It has nothing to do with SQL injection. I clearly mentioned it on XSS.

  • #14
    Regular Coder
    Join Date
    Nov 2012
    Posts
    109
    Thanks
    6
    Thanked 12 Times in 12 Posts
    Well yeah, but you were referring to PDO statements, which are used for database.

    But, that's besides the original subject of your thread, so lets drop the discussion

    Getting back to your original post though, are you using MySQL or MySQLi? If the answer is MySQL, you should really switch to using the MySQLi library. For one PHP will drop support on MySQL with the next major release (or so the rumours go), secondly it support OOP and thirdly it has real_escape_string to Oh, and it's just generally better, faster and safer. Or so they say.

  • #15
    Regular Coder
    Join Date
    Sep 2011
    Posts
    310
    Thanks
    23
    Thanked 0 Times in 0 Posts
    I am planning to use MySQLi in the future. I do web design more than programming. And its for fun only as programming is boring lol. Im more on web marketing, marketing, office and little desktop publishing.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •