February 6th, 2014, 05:27 PM
Tp prepare or to prepare that is the question
Well if have not be as good of a boy as I should have been I must admit. So after reading the tough stance members here have on security I decided to revisit my thoughts on it.
It is clear that the makers of PHP (and people everywhere) believe that to protect against sql injections the prepare statement is the way to go. I look and read and I still do not get it, it makes no since what so ever to execute a sql command knowing it "had" attempted malicious code.
It makes much more since to have a command that checks to see if the post variable contains malicious code and if it does do not even use it in a query and the programmer could return (or not) a "Sorry invalid request".
Please tell me what I do not get here
February 6th, 2014, 05:34 PM
Why do you think that it's easier to tell you if there's "invalid" content rather then escaping the given content?
Think about this scenario... I an writing an article on database security, so I need to add in some text that include what would normally be a DB hack. Using your method I'd never be ale to because it would be checked and the "invalid" text would not be allowed. Using prepared statements, the input would be allowed, and the input would be escaped so that any possible threat was negated. In this scenario, prepared statements work to get the job done safely, but your solution blocks it every time.
You are still free to do your own checking, but I'll guarantee that you'll never be able to think of every attack vector out there as it's very complicated. That's why you escape/parse all data that's stored in your database.
February 6th, 2014, 05:42 PM
I did not think I could create a check for myself I was stating that if the makers of PHP would determine what to "encapsulate" for their prepare statement then THEY could and would know what is malicious code to use for a command to check instead.
OK, I did think that for the case that someone wanted to give an example of hacker code that it would not work but for me that was such a small number of people that letting hacker bang against the server trying to get in was not worth it
February 6th, 2014, 05:55 PM
It's not quite as easy as saying "detect malicious code". There's a few standard things that work, but these are mostly things like
But, there's also other things that can be done, but no matter what you do, there's always a use-case where you want things like those to be allowed, but to be sanitised so that they are safe.
'; DROP TABLE `users`;
Real security isn't flagging something that's possibly malicious. It's ensuring that everything that you process has any possible malicious content sanitised before you do anything with it. You can always do checks later, but you need to ensure that you're dealing with the data the correct way in the first place.
February 6th, 2014, 06:50 PM
the whole idea of blocking “evil input” is conceptually wrong and shows a misunderstanding of the problem.
There's no such thing as “evil input”. It's all just data. If your application does funky stuff with this data, that's definitely your fault, not the user's.
SQL injections are actually just a symptom of an application bug. The real problem is that your application incorrectly treats the input data as part of the SQL query. That's what needs to be fixed. If you try to prevent people from exploiting this bug, you merely cover up the symptoms.
So SQL injections (or any injection attack) are an interpretation problem, not an input problem. The users can write down anything they want. It's your job to make sure the data is actually interpreted as data and not as executable code. In the context of SQL queries, the most reliable solution for that is indeed a prepared statement.
Blacklisting in general is a very poor approach. Many people have tried to write a filter like you describe, and it has always ended in a disaster. A typical example is the cross-site scripting filter of the CodeIgniter framework: While the developers desperately tried to update the rules every time somebody found a new exploit, the filter became more and more complex. Now it has reached the point where even the core developers no longer understand it. And I'm pretty sure it still doesn't work.
Compare this to correct method: If you simply wrap the input in htmlspecialchars() so that it will be treated as data rather than HTML markup, you get perfect protection with a single function call. No complex code, no arms race with attackers. It just works, because it actually solves the underlying problem.
Last edited by Jacques1; February 6th, 2014 at 06:53 PM.
February 8th, 2014, 10:17 AM
Absolutely! But I think there is an Oops! here...
Originally Posted by Jacques1
This function makes characters like & and < appear in html; it doesn't properly quote the string for SQL -- you want
mysql_real_escape_string() or similar.
Comments on this post
February 8th, 2014, 11:48 AM
Originally Posted by Jacques1
Two different topics.
Originally Posted by Jacques1
February 10th, 2014, 02:37 PM
Prepared statemnts as I understand them go down to the query compile level on the database server. In other words we aren't talking about "escaping" input, the values are not compiled as SQL code but put in as known data values once the query is compiled in MySQL (or whatever db you are using). Using prepared statements isn't the same thing as using mysql_real_escape_string on input. The db server knows you meant them as values and doesn't execute them as code but just uses them as raw data for the compiled query. This is how it was explained to me years ago when I started using PS.
Comments on this post
Last edited by Hammer65; February 10th, 2014 at 02:41 PM.
February 10th, 2014, 03:03 PM
This isn't always true in the world of PHP.
By default, the PDO extension does in fact escape the values when it executes a prepared statement. This is of course much less secure than an actual prepared statement and can lead to SQL injection vulnerabilities. This behaviour must be turned off explicitly through the PDO::ATTR_EMULATE_PREPARES parameter.
So there are actually two types of prepared statements: Real prepared statements which consist of two transmissions (the prepared statement and the data). And fake prepared statements which happen client-side and only send a single query to the database system.
Fortunately, there's an easy test to check which one you have. Simply include a syntax error in the statement. If the error message is shown during preparation, you have a real prepared statement, because the DBMS has obviously parsed the statement. If the error only happens when you execute the statement, you have a fake one, because the preparetion has happend client-side.
February 10th, 2014, 03:19 PM
Now this would be a great reason to use prepared statements and will do so for now on, thanks
Originally Posted by Hammer65
February 10th, 2014, 03:26 PM
Um, what? I have no idea how that post brought any new information. But if you're convinced now that your filtering stuff is a bad idea, I guess we can be happy.
February 10th, 2014, 03:38 PM
Because as my OP said I based the fact that if PHP could solve the malicious code with a function they have created such as mysql_real_escape_string then I could use that function to determine if the input was malicious. Then instead of running the query with it I would just return "input no good".
The new information that no php function solves that issues and tha prepared statements in fact makes mysql "deal" with the data in a different manor changes everything.
In my case this is not as big of an issue as other as all the browser based software I create is not available to the general public only to businesses that pay as a SAAS model but I still like to do things right so that is why I asked.
Even as old as I am it is nice to try to learn more.
February 10th, 2014, 03:56 PM
So when you talked about prepared statements in your OP, you didn't even know what that term means, and you didn't bother to look it up at Wikipedia?
Next time, it might be helpful to understand the concepts before you argue against them.