#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Location
    Laguna Niguel, Ca USA
    Posts
    12
    Rep Power
    0

    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
  2. #2
  3. Code Monkey V. 0.9
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2005
    Location
    A Land Down Under
    Posts
    2,122
    Rep Power
    1990
    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.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Location
    Laguna Niguel, Ca USA
    Posts
    12
    Rep Power
    0
    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
  6. #4
  7. Code Monkey V. 0.9
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2005
    Location
    A Land Down Under
    Posts
    2,122
    Rep Power
    1990
    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

    Code:
    '; DROP TABLE `users`;
    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.

    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.
  8. #5
  9. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    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 05:53 PM.
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2014
    Location
    Sano, Japan
    Posts
    3
    Rep Power
    0
    Originally Posted by Jacques1
    Hi,
    the whole idea of blocking “evil input” is conceptually wrong and shows a misunderstanding of the problem.
    Absolutely! But I think there is an Oops! here...

    Compare this to correct method: If you simply wrap the input in htmlspecialchars() ...
    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

    • Jacques1 disagrees
  12. #7
  13. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by Jacques1
    In the context of SQL queries, the most reliable solution for that is indeed a prepared statement.
    Originally Posted by Jacques1
    A typical example [for a blacklist] is the cross-site scripting filter of the CodeIgniter framework [...] Compare this to correct method: If you simply wrap the input in htmlspecialchars() [...]
    Two different topics.
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  14. #8
  15. Web Developer/Musician
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Nov 2004
    Location
    Tennessee Mountains
    Posts
    2,408
    Rep Power
    1031
    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.

    http://en.wikipedia.org/wiki/Prepared_statement

    Comments on this post

    • DenRomano agrees
    Last edited by Hammer65; February 10th, 2014 at 01:41 PM.
  16. #9
  17. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    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.
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Location
    Laguna Niguel, Ca USA
    Posts
    12
    Rep Power
    0
    Originally Posted by Hammer65
    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.

    http://en.wikipedia.org/wiki/Prepared_statement
    Now this would be a great reason to use prepared statements and will do so for now on, thanks
  20. #11
  21. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    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.
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Location
    Laguna Niguel, Ca USA
    Posts
    12
    Rep Power
    0
    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.
  24. #13
  25. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    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.
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".

IMN logo majestic logo threadwatch logo seochat tools logo