3 ms·

So why can't I use mysql_real_escape_string? Let's assume that I have to use PHP and there's no way in hell this app is ever going to need to be ported to a dif

So why can't I use mysql_real_escape_string? Let's assume that I have to use PHP and there's no way in hell this app is ever going to need to be ported to a different db. (Or wrap it in a "quote" method.) Edit: Instead of a downmod, how about an answer?

tetha 17y ago
I think, escaping the input from the user in some way is a way which might work, but basically does not address the core issue of sql-injection attacks, pretty much like putting transparent tape over a crack in a window. It sort of works against most known cracks, but there might (and in security-terms, that means: there will) be a crack which cannot be taped well. But, analogies are no good, let's get to the core of this. Executing an sql-statement basically means that the server parses, optimizes and executes the statement. Whenever you type something like "Select name from dogs where tag=" + mysql_real_escape_string($user_tag) you assume: the server will parse this query as a select-statement, with the field 'name' selected, whereas the table to examine is 'dogs' and there is some predicate on the tag, and some properly secured value goes into the predicate. If an injection attack is successful, the assumption that the parsed SQL-statement and the SQL-statement in the code match fails, because a successful SQL-injection infiltrates the actual structure of an SQL-statement and modifies it pretty much arbitrarily. Now, it is true that it might be possible to escape all known possible user inputs (which might be possible, as SQL should not be turing complete, but I am not sure about the newest standards), so the user input cannot infiltrate the structure of the SQL-Query, because in the user input, no control characters are working. But this is, as I said, mostly like saying: Well, there is a hedgedog on your chair, so use a pillow before you sit down or it might hurt both of you. It does not address the core of the problem. The core of the problem is: you are doing two things at once. You are transmitting the actual (static) query you want to execute -- the structure of the query -- and the actual values in a single go. The real way -- parametrized queries -- first transmit the structure and after this, they transmit the value. Thus, it is guaranteed that the parsed structure in the database will be exactly the structure you specified in your code, and thus, an attacker cannot modify the structure of your query by inserting values into the structure, because at this point, it is clear that the values are values, and not actual structure code. And this is the reason, why parametrized queries are better than assuming that a certain function (or multiple functions, who knows) can handle ALL possible injection attacks any hacker (or cracker :/) genius might ever think of. Transferring the structure first and the values separately is like picking the hedgehog up, sitting down and having a happy hedgehog sitting on your lap ;) Note that I am aware of the problem that this just guarantees that the structure of the query is transmitted properly. If the structure of your query allows arbitrary behaviour of your query via appropiate values ('if param1 == "admin" then eval param2' sort of rings), then attacks are possible again. However, these attacks are different from the sql structure injections, because these attacks do not aim to modify the structure of your query, but they rather aim to abuse the behaviour of your query. If you like to think in analogies, a structure injection would be like patching the linux kerlen somehow to get a backdoor going, while a value attack against a query with dynamic behaviour is more like replacing the 0-page with arbitray code doing nasty things. Also note that not using parametrized queries with dynamic behaviour opens up two attack vectors: Attacking the structure of your query (bobby tables) and abusing the behaviour of your queries. And also note, that securing a single query (or, all queries in your application) is also no substitute for actually securing your database server, because overall, your data is the goal of an attacker. The data is the money, not the application. Thus, your application is just another attack vector against your actual data base and your actual data, and your application consists of more or less attack vectors (which might be injection attacks, behaviour attacks, attacks via session hijacking, ...). Thus, you might use good queries, but if every user has weak passwords and also maximum privileges in the database, an attacker will ignore the application and probably try to attack the database directly. Sorry for the wall of text, I just wanted to answer this and did not have that much time to make it shorter, HTH, Tetha

tetha 17y ago

I think, escaping the input from the user in some way is a way which might work, but basically does not address the core issue of sql-injection attacks, pretty much like putting transparent tape over a crack in a window. It sort of works against most known cracks, but there might (and in security-terms, that means: there will) be a crack which cannot be taped well. But, analogies are no good, let's get to the core of this. Executing an sql-statement basically means that the server parses, optimizes and executes the statement. Whenever you type something like "Select name from dogs where tag=" + mysql_real_escape_string($user_tag) you assume: the server will parse this query as a select-statement, with the field 'name' selected, whereas the table to examine is 'dogs' and there is some predicate on the tag, and some properly secured value goes into the predicate. If an injection attack is successful, the assumption that the parsed SQL-statement and the SQL-statement in the code match fails, because a successful SQL-injection infiltrates the actual structure of an SQL-statement and modifies it pretty much arbitrarily. Now, it is true that it might be possible to escape all known possible user inputs (which might be possible, as SQL should not be turing complete, but I am not sure about the newest standards), so the user input cannot infiltrate the structure of the SQL-Query, because in the user input, no control characters are working. But this is, as I said, mostly like saying: Well, there is a hedgedog on your chair, so use a pillow before you sit down or it might hurt both of you. It does not address the core of the problem. The core of the problem is: you are doing two things at once. You are transmitting the actual (static) query you want to execute -- the structure of the query -- and the actual values in a single go. The real way -- parametrized queries -- first transmit the structure and after this, they transmit the value. Thus, it is guaranteed that the parsed structure in the database will be exactly the structure you specified in your code, and thus, an attacker cannot modify the structure of your query by inserting values into the structure, because at this point, it is clear that the values are values, and not actual structure code. And this is the reason, why parametrized queries are better than assuming that a certain function (or multiple functions, who knows) can handle ALL possible injection attacks any hacker (or cracker :/) genius might ever think of. Transferring the structure first and the values separately is like picking the hedgehog up, sitting down and having a happy hedgehog sitting on your lap ;) Note that I am aware of the problem that this just guarantees that the structure of the query is transmitted properly. If the structure of your query allows arbitrary behaviour of your query via appropiate values ('if param1 == "admin" then eval param2' sort of rings), then attacks are possible again. However, these attacks are different from the sql structure injections, because these attacks do not aim to modify the structure of your query, but they rather aim to abuse the behaviour of your query. If you like to think in analogies, a structure injection would be like patching the linux kerlen somehow to get a backdoor going, while a value attack against a query with dynamic behaviour is more like replacing the 0-page with arbitray code doing nasty things. Also note that not using parametrized queries with dynamic behaviour opens up two attack vectors: Attacking the structure of your query (bobby tables) and abusing the behaviour of your queries. And also note, that securing a single query (or, all queries in your application) is also no substitute for actually securing your database server, because overall, your data is the goal of an attacker. The data is the money, not the application. Thus, your application is just another attack vector against your actual data base and your actual data, and your application consists of more or less attack vectors (which might be injection attacks, behaviour attacks, attacks via session hijacking, ...). Thus, you might use good queries, but if every user has weak passwords and also maximum privileges in the database, an attacker will ignore the application and probably try to attack the database directly. Sorry for the wall of text, I just wanted to answer this and did not have that much time to make it shorter, HTH, Tetha