Menu
Community /Pin to ProfileBookmark
@NogDogFeb 15.2012 — #Properly using the applicable escape mechanism (e.g. mysql_real_escape_string()) can be just as safe. However, bound parameters within prepared statements put less onus on the developer to make sure data is escaped correctly and quoted if/when necessary -- the parameter binding does it automatically. I've seen systems that their db classes already did the parameter massaging for you when you do the prepared statement. However, I still prefer massaging the data myself. If they have it in the deeper levels that's great. Double security Oh ok I see. So I guess the main benefit of prepared statements over regular queries is performance rather than security.
Thank you very much,
PDO is a class, so it maintains all of that information by itself. It's much easier to debug and more secure as a result, since it's more difficult to rewrite the variable and not notice that it happens.
And while you can write mysql_real_escape_string() everywhere, reality is that you may 'forget'. PDO makes the chance of that happening much less. It's also easier to write PDO queries, since they are shorter.
PDO prepared statements aren't of course ideal, with large enough queries it may be more difficult to keep track of which variable goes where, but PDO has options where you don't necessarily have to use question marks instead of variables, so there are options covering that too.
To sum things up, prepared statements and using PDO makes debugging database issues easier, makes code faster and more secure due to reducing programmer error and has an added bonus of supporting other database drivers, should you need to connect to another database (you would not have to re-invent the wheel or learn new set of functions).
One more thing: I was one of those developers who thought mysql_* is all I need. I even made a class that kept track of my connection keys and queries to reduce some of the potential problems. But that's just delaying the inevitable refactoring, reality is that you are better off using PDO.
[I]I feel like I've become a PDO salesman! ?[/I]
Hehe, you were pretty convincing! Thanks for the clear explanation. I'm definitely considering prepared statements.
Basically it boils down to:
pdo->quote = mysql_real_escape_string
pdo->[query | exec] = mysql_query
The rest is just a toolbox of ridiculously powerful toys for transforming information inside of queries and results.
I suppose a word on prepared statements and security:
They help because the SQL (logic) is kept separate from the data (input). This is basically the weakness that sql injection exploits. It's the same idea as XSS and buffer overflows. The data, or arguments, should be kept separate from the logic (code). For XSS the output is mixed with mark-up because the output wasn't properly encoded for display in the mark-up. For buffer overflows the return address on the top of the stack is overwritten at some point by escaping a buffer, when a function is popped off the stack it will return to whichever address you want (this can get messy, better just leave it at this). Integer overflow is an interesting one because it does not directly involve a string: -1 = 2^bits ... or ... unsignedInt = -1.
Edit: Please don't make everything a prepared statement. There's a time and place for them sure, but it's not magic... just the closest thing to it.@NogDogFeb 15.2012 — #
Just make sure you are not "double escaping" your input strings. If you do, you'll end up with escaped escape characters, causing back-slashes (at least in the case of MySQL) to end up in the actual data.I have already programmed a significant part of my application, using regular mysql_* functions. Do you think it's gonna be a hassle to change all the php code to prepared statements and PDO?
It depends. If your application is procedural and not object-oriented, especially if database connection and queries are not object oriented, then it will be difficult to convert and make sure nothing breaks.
But if you have a database class that uses mysql_* for queries and connections, then converting that to PDO will be relatively simple and other parts of the system stay in tact. I did that myself about a week ago on a system I am running and conversion took about 30 minutes.I don't exactly understand the "procedural" term. I don't think what I'm doing is object oriented, because I haven't created objects.
My application is a sort of e-commerce application with a database (with data such as users, personal details, sold items, categories etc..). And in my scripts, I'm using very simple database connection, with mysql_* functions. I escape using mysql_real_escape_string().
I use those SQL queries for very simple tasks, such as displaying categories, items, personal details etc...
I try to write the application as simply as possible.
Is this procedural?
What would you recommend?
Thanks for your help
Eric
Procedural is basically an application that runs in a 'stream' of sorts. It loads bunch of files, based on user input calls various methods and does its job. Signs of a procedural code are usually large 'functions' files that are being included (declaring a lot of functions even if those functions are not used) as well as use of globals and variables that are difficult to track down. Procedural code does not really have 'components', it all acts as one entity (in a way, as 'one messy object'). When you have to replace one part you have to test the entire system to make sure nothing breaks apart.
Procedural vs Object Oriented programming, in PHP case, does not play a huge role. Even highly experienced developers write procedural code sometimes. This means that understanding the benefits of OOP as opposed to procedural is not just about reading a book about OOP. You can get great results done with procedural code. But if you start using a lot of classes (like various open source components such as PHPMailer) you'll realize at some point how efficient it is to have parts of code to work this way, since the main advantage of Object Oriented Programming is that you can take and replace a component without having to worry about other parts of the program breaking apart and you can use the same 'class' in other applications as well.
OOP code is also much more optimized, using MVC architecture and Factory pattern (if you want to check out an example, check out my framework in my signature) you can write code and load components dynamically, always making sure that your application does not load more resources than it needs.
But I've got to be honest with you, getting from procedural to OOP takes a long time if you did not start with OOP from the beginning. I worked for years and barely used OOP in my PHP applications since I did not 'see' the benefit, even though I read books about it. But the benefit is there if you intend to maintain your code for a long time or if you have to replace a component at some point along the way (such as - in this case - a database driver).
PDO is also small step towards OOP, since PDO is a class and using PDO you would be using an object as opposed to function library such as mysql_*. But if your code is mostly procedural, then it is difficult to carry on PDO without resorting to other dirty tricks, such as using globals and I believe it will be tough to refactor your code to PDO.Thanks a lot kristovaher for your explanation. It introduced me to new concepts that I need to research.
I am obviously a beginner but I am really willing to learn how to program powerful stuff, acquire new knowledge, have good practices etc.. I don't like avoiding difficulties because I feel I'm missing out great stuff to learn.
Even though my application is not gonna be that big, (I'll use PHPmailer and that PEAR image resizing class, forgot the name) I want to make the best application I can. I am currently going through PDO and prepared statement tutorials. Is there any other concept that I should look at after that?
Thanks again, your help is very much appreciated.
I'm glad that I've been of assistance. I know it is a lot to handle right now, but I've been where you're at and you should not think of what you are doing today as 'bad' in any way (that is simply demotivational).
When it comes to starting to use OOP more extensively, you should start with small things. If you rush and try to write majority of your code as object oriented right away, you are bound to make mistakes (and not just mistakes, but horrible mistakes that will make you hate programming).
What you should start is little things, maybe make a database class for yourself and use that. Study the code and go through variety of tutorials online about OOP coding. But know that it won't change overnight, it might easily take a year before everything finally clicks into place and you're able to write and plan for efficient object oriented code.
Why a year? I remember when I first thought that now I've got it and I know how OOP works. I wrote a framework. And I rewrote that same framework three times in a year, simply because I realized that I either messed up performance or simply did things wrong. At the time it seemed 'correct', but after a while you see the flaws and problems in your classes and objects and that will start rubbing you the wrong way.
But yes, take it easy, change won't happen overnight and the best thing about PHP is that you can still write great applications procedurally and have fun while at it. As long as you continue learning and try to do better every time you work on a new project, you'll get there sooner rather than later.I hear you, I think I will implement PDO and prepared statements in my application, but will first learn all the implications to be sure what I'm doing.
I think it will be a slow but reasonable start into OOP.
Thanks again
Regards,
Eric
[RESOLVED] Why would a prepare statement be safer?
Hi,
I searched many forums about prepared statements security and it appears that they are “much safer” than regular SQL queries.
But why would prepared statement be safer since, no matter what you do, you’re always gonna have to take the user’s input into the database. Prepared statement are supposed to prevent that, but how can you possibly prevent that?! It seems impossible to me beginner!
Do you think using mysql_real_escape_string() with regular query is just as safe as prepared statements?
Thanks in advance!
Sign in
to post a comment14 Comments(s) ↴
0
@NogDogFeb 15.2012 — #reply?
0
@ssystemsFeb 15.2012 — #reply?
0
@eric01authorFeb 15.2012 — #Thank you very much,
reply?
0
@kristovaherFeb 15.2012 — #The problem with mysql_* set of functions is that they are procedural and majority of developers don't even store the connection key when creating MySQL connections (When using mysql_* functions you should always send connection key together with the function to be sure what connection the method call is for). mysql_* functions pick the last-known connection key when executing these statements, which means that if any script creates a new connection at some point before your mysql_* calls, your entire site will break. If your code consists of mostly procedural architecture, then it's nearly impossible not to build a 'hack' if you wish to connect to another database at some point (which is often crucial for infosystems).PDO is a class, so it maintains all of that information by itself. It's much easier to debug and more secure as a result, since it's more difficult to rewrite the variable and not notice that it happens.
And while you can write mysql_real_escape_string() everywhere, reality is that you may 'forget'. PDO makes the chance of that happening much less. It's also easier to write PDO queries, since they are shorter.
PDO prepared statements aren't of course ideal, with large enough queries it may be more difficult to keep track of which variable goes where, but PDO has options where you don't necessarily have to use question marks instead of variables, so there are options covering that too.
To sum things up, prepared statements and using PDO makes debugging database issues easier, makes code faster and more secure due to reducing programmer error and has an added bonus of supporting other database drivers, should you need to connect to another database (you would not have to re-invent the wheel or learn new set of functions).
One more thing: I was one of those developers who thought mysql_* is all I need. I even made a class that kept track of my connection keys and queries to reduce some of the potential problems. But that's just delaying the inevitable refactoring, reality is that you are better off using PDO.
[I]I feel like I've become a PDO salesman! ?[/I]
reply?
0
@eric01authorFeb 15.2012 — #
[I]I feel like I've become a PDO salesman! ?[/I][/QUOTE]
Hehe, you were pretty convincing! Thanks for the clear explanation. I'm definitely considering prepared statements.
reply?
0
@eval_BadCode_Feb 15.2012 — #It only helps you. You can use PDO in a stupid way and certainly allow for sql injection: pdo->query($_POST['query']);Basically it boils down to:
pdo->quote = mysql_real_escape_string
pdo->[query | exec] = mysql_query
The rest is just a toolbox of ridiculously powerful toys for transforming information inside of queries and results.
I suppose a word on prepared statements and security:
They help because the SQL (logic) is kept separate from the data (input). This is basically the weakness that sql injection exploits. It's the same idea as XSS and buffer overflows. The data, or arguments, should be kept separate from the logic (code). For XSS the output is mixed with mark-up because the output wasn't properly encoded for display in the mark-up. For buffer overflows the return address on the top of the stack is overwritten at some point by escaping a buffer, when a function is popped off the stack it will return to whichever address you want (this can get messy, better just leave it at this). Integer overflow is an interesting one because it does not directly involve a string: -1 = 2^bits ... or ... unsignedInt = -1.
Edit: Please don't make everything a prepared statement. There's a time and place for them sure, but it's not magic... just the closest thing to it.
reply?
0
@NogDogFeb 15.2012 — #I've seen systems that their db classes already did the parameter massaging for you when you do the prepared statement. However, I still prefer massaging the data myself. If they have it in the deeper levels that's great. Double security[/QUOTE]
Just make sure you are not "double escaping" your input strings. If you do, you'll end up with escaped escape characters, causing back-slashes (at least in the case of MySQL) to end up in the actual data.
reply?
0
@eric01authorFeb 15.2012 — #reply?
0
@kristovaherFeb 15.2012 — #I have already programmed a significant part of my application, using regular mysql_* functions. Do you think it's gonna be a hassle to change all the php code to prepared statements and PDO?[/QUOTE]
It depends. If your application is procedural and not object-oriented, especially if database connection and queries are not object oriented, then it will be difficult to convert and make sure nothing breaks.
But if you have a database class that uses mysql_* for queries and connections, then converting that to PDO will be relatively simple and other parts of the system stay in tact. I did that myself about a week ago on a system I am running and conversion took about 30 minutes.
reply?
0
@eric01authorFeb 15.2012 — #My application is a sort of e-commerce application with a database (with data such as users, personal details, sold items, categories etc..). And in my scripts, I'm using very simple database connection, with mysql_* functions. I escape using mysql_real_escape_string().
I use those SQL queries for very simple tasks, such as displaying categories, items, personal details etc...
I try to write the application as simply as possible.
Is this procedural?
What would you recommend?
Thanks for your help
Eric
reply?
0
@kristovaherFeb 15.2012 — #I don't exactly understand the "procedural" term. I don't think what I'm doing is object oriented, because I haven't created objects.
My application is a sort of e-commerce application with a database (with data such as users, personal details, sold items, categories etc..). And in my scripts, I'm using very simple database connection, with mysql_* functions. I escape using mysql_real_escape_string().
I use those SQL queries for very simple tasks, such as displaying categories, items, personal details etc...
I try to write the application as simply as possible.
Is this procedural?
What would you recommend?
Thanks for your help
Eric[/QUOTE]
Procedural is basically an application that runs in a 'stream' of sorts. It loads bunch of files, based on user input calls various methods and does its job. Signs of a procedural code are usually large 'functions' files that are being included (declaring a lot of functions even if those functions are not used) as well as use of globals and variables that are difficult to track down. Procedural code does not really have 'components', it all acts as one entity (in a way, as 'one messy object'). When you have to replace one part you have to test the entire system to make sure nothing breaks apart.
Procedural vs Object Oriented programming, in PHP case, does not play a huge role. Even highly experienced developers write procedural code sometimes. This means that understanding the benefits of OOP as opposed to procedural is not just about reading a book about OOP. You can get great results done with procedural code. But if you start using a lot of classes (like various open source components such as PHPMailer) you'll realize at some point how efficient it is to have parts of code to work this way, since the main advantage of Object Oriented Programming is that you can take and replace a component without having to worry about other parts of the program breaking apart and you can use the same 'class' in other applications as well.
OOP code is also much more optimized, using MVC architecture and Factory pattern (if you want to check out an example, check out my framework in my signature) you can write code and load components dynamically, always making sure that your application does not load more resources than it needs.
But I've got to be honest with you, getting from procedural to OOP takes a long time if you did not start with OOP from the beginning. I worked for years and barely used OOP in my PHP applications since I did not 'see' the benefit, even though I read books about it. But the benefit is there if you intend to maintain your code for a long time or if you have to replace a component at some point along the way (such as - in this case - a database driver).
PDO is also small step towards OOP, since PDO is a class and using PDO you would be using an object as opposed to function library such as mysql_*. But if your code is mostly procedural, then it is difficult to carry on PDO without resorting to other dirty tricks, such as using globals and I believe it will be tough to refactor your code to PDO.
reply?
0
@eric01authorFeb 15.2012 — #I am obviously a beginner but I am really willing to learn how to program powerful stuff, acquire new knowledge, have good practices etc.. I don't like avoiding difficulties because I feel I'm missing out great stuff to learn.
Even though my application is not gonna be that big, (I'll use PHPmailer and that PEAR image resizing class, forgot the name) I want to make the best application I can. I am currently going through PDO and prepared statement tutorials. Is there any other concept that I should look at after that?
Thanks again, your help is very much appreciated.
reply?
0
@kristovaherFeb 15.2012 — #Thanks a lot kristovaher for your explanation. It introduced me to new concepts that I need to research.
I am obviously a beginner but I am really willing to learn how to program powerful stuff, acquire new knowledge, have good practices etc.. I don't like avoiding difficulties because I feel I'm missing out great stuff to learn.
Even though my application is not gonna be that big, (I'll use PHPmailer and that PEAR image resizing class, forgot the name) I want to make the best application I can. I am currently going through PDO and prepared statement tutorials. Is there any other concept that I should look at after that?
Thanks again, your help is very much appreciated.[/QUOTE]
I'm glad that I've been of assistance. I know it is a lot to handle right now, but I've been where you're at and you should not think of what you are doing today as 'bad' in any way (that is simply demotivational).
When it comes to starting to use OOP more extensively, you should start with small things. If you rush and try to write majority of your code as object oriented right away, you are bound to make mistakes (and not just mistakes, but horrible mistakes that will make you hate programming).
What you should start is little things, maybe make a database class for yourself and use that. Study the code and go through variety of tutorials online about OOP coding. But know that it won't change overnight, it might easily take a year before everything finally clicks into place and you're able to write and plan for efficient object oriented code.
Why a year? I remember when I first thought that now I've got it and I know how OOP works. I wrote a framework. And I rewrote that same framework three times in a year, simply because I realized that I either messed up performance or simply did things wrong. At the time it seemed 'correct', but after a while you see the flaws and problems in your classes and objects and that will start rubbing you the wrong way.
But yes, take it easy, change won't happen overnight and the best thing about PHP is that you can still write great applications procedurally and have fun while at it. As long as you continue learning and try to do better every time you work on a new project, you'll get there sooner rather than later.
reply?
0
@eric01authorFeb 15.2012 — #I think it will be a slow but reasonable start into OOP.
Thanks again
Regards,
Eric
reply?