6 ms·

Well maybe you should check that again. Your approach sounds like you should have stored procs instead. Using prepared statements or variable binding to fight

by Morg 14y ago

Well maybe you should check that again. Your approach sounds like you should have stored procs instead. Using prepared statements or variable binding to fight SQL injections is not the best idea, although its widespread. In most cases where you want a prepared statement, you'd be better off using a stored proc, as you'll skip the expensive optimization every single time. MySQL is not even a real RDBMS (no ACID, no triggers, fail APIs, etc.), anyone using it should switch to PostgreSQL yesterday unless their data really doesn't matter. SQL injections are 100% avoided by user input control in the application, and the simplest way is to escape all escape characters, that may require reading a bit of doc but w/e.

deleted 14y ago
[deleted]
- Morg 14y ago
  Indeed it would affect everyone just as much - still, I will spread MySQL knowledge (not hate) whether or not there is a reason for it. And I'll say it time and time again, If you think MySQL does not have major issues as a DBMS, you should not make database-related decisions as your knowledge is too limited to make a sensible decision. Just like windows. The real Windows experts can both tell you how much it's made of fail and fix your issues, the others are charlatans.
ironchef 14y ago
"Your approach sounds like you should have stored procs instead." Not necessarily. As one moves towards stored procs, it becomes more common to place business logic in said stored procs. This tends to go against MVC and also limits some scaling options (as your business logic then is executing in your DB). They also often will tie you to a singular DB...making moving DBs more painful. "Using prepared statements or variable binding to fight SQL injections is not the best idea, although its widespread." I read it not as _the_ best idea, but as yet another layer to potentially catch something. Belt _and_ suspenders if you will. Don't get me wrong. I'm not saying prepared statements are the greatest thing since sliced bread; however, in oracle or postgresql backed apps, they're a best practice to investigate.
- Morg 14y ago
  two roundtrips to the database where one should do = bad desingn imho.
sk5t 14y ago
I am weary of reading that every RDBMS does an expensive query-plan calculation on every non-sproc DML... maybe you know of a few that behave this way, but there are plenty that don't. Alternately, suppose one would like to use SQLite with a competent ORM--what's the harm in that?
fendale 14y ago
> Using prepared statements or variable binding to fight SQL injections is not the best idea What is the best idea then? If you bind variables to SQL statements, you are SQL injection safe 100% of the time. There is no crafty input sequence that can fool anything. > In most cases where you want a prepared statement, you'd be better off using a stored proc, as you'll skip the expensive optimization every single time. I am only qualified to speak about Oracle which is a DB I know extremely well. A query is a query, whether it comes from Java, Perl, Ruby or inside of a stored proc. If you prepare a statement once, and then cache that handle and execute it many times, you optimize the query one time. Also in Oracle, if you prepare-bind-execute one time only, the next time you do the same sequence of steps you Oracle doesn't have to optimize the query again - it can spot it is the same as a previous query and short circuit the process. Mysql I think doesn't cache SQL statements for later reuse like Oracle, which is why binding isn't as important for performance (in Oracle, not bind queries is a pretty good way to bring the database to its knees) - but its still essential for security. > SQL injections are 100% avoided by user input control in the application, and the simplest way is to escape all escape characters What this bug has just proven, is that this escaping is not all that easy - crafty attackers can come up will all sorts of strings that seem to work around the escaping time and time again.
- Morg 14y ago
  Even Oracle has to rerun optimization now and then, a stored proc is like a prepared statement except it's stored, i.e. It's not affected by a restart afaik. It has the major difference that you can run your stored proc for one record anytime and gain performance, whereas prepared statements are usually prepared just in time - preparing them / reusing them on the long term really is a bad implementation of stored procs. MySQL is a toy, it's not SQL compliant by a long shot and most features it implements are incomplete, the fact that stored procs or prepared statements are part of that comes as no surprise to me. What this bug has just proven, is that not everyone is capable of writing an escape string. That doesn't change the fact that it's a zillion times cheaper to sanitize then call rather than call to prepare then call to execute, especially more so if you're not using stored porcedures and have to prepare / check if prepared.

by Morg 14y ago

deleted 14y ago

[deleted]

Morg 14y ago
Indeed it would affect everyone just as much - still, I will spread MySQL knowledge (not hate) whether or not there is a reason for it. And I'll say it time and time again, If you think MySQL does not have major issues as a DBMS, you should not make database-related decisions as your knowledge is too limited to make a sensible decision. Just like windows. The real Windows experts can both tell you how much it's made of fail and fix your issues, the others are charlatans.

ironchef 14y ago

"Your approach sounds like you should have stored procs instead." Not necessarily. As one moves towards stored procs, it becomes more common to place business logic in said stored procs. This tends to go against MVC and also limits some scaling options (as your business logic then is executing in your DB). They also often will tie you to a singular DB...making moving DBs more painful. "Using prepared statements or variable binding to fight SQL injections is not the best idea, although its widespread." I read it not as _the_ best idea, but as yet another layer to potentially catch something. Belt _and_ suspenders if you will. Don't get me wrong. I'm not saying prepared statements are the greatest thing since sliced bread; however, in oracle or postgresql backed apps, they're a best practice to investigate.

Morg 14y ago
two roundtrips to the database where one should do = bad desingn imho.

sk5t 14y ago

I am weary of reading that every RDBMS does an expensive query-plan calculation on every non-sproc DML... maybe you know of a few that behave this way, but there are plenty that don't. Alternately, suppose one would like to use SQLite with a competent ORM--what's the harm in that?

fendale 14y ago

> Using prepared statements or variable binding to fight SQL injections is not the best idea What is the best idea then? If you bind variables to SQL statements, you are SQL injection safe 100% of the time. There is no crafty input sequence that can fool anything. > In most cases where you want a prepared statement, you'd be better off using a stored proc, as you'll skip the expensive optimization every single time. I am only qualified to speak about Oracle which is a DB I know extremely well. A query is a query, whether it comes from Java, Perl, Ruby or inside of a stored proc. If you prepare a statement once, and then cache that handle and execute it many times, you optimize the query one time. Also in Oracle, if you prepare-bind-execute one time only, the next time you do the same sequence of steps you Oracle doesn't have to optimize the query again - it can spot it is the same as a previous query and short circuit the process. Mysql I think doesn't cache SQL statements for later reuse like Oracle, which is why binding isn't as important for performance (in Oracle, not bind queries is a pretty good way to bring the database to its knees) - but its still essential for security. > SQL injections are 100% avoided by user input control in the application, and the simplest way is to escape all escape characters What this bug has just proven, is that this escaping is not all that easy - crafty attackers can come up will all sorts of strings that seem to work around the escaping time and time again.

Morg 14y ago
Even Oracle has to rerun optimization now and then, a stored proc is like a prepared statement except it's stored, i.e. It's not affected by a restart afaik. It has the major difference that you can run your stored proc for one record anytime and gain performance, whereas prepared statements are usually prepared just in time - preparing them / reusing them on the long term really is a bad implementation of stored procs. MySQL is a toy, it's not SQL compliant by a long shot and most features it implements are incomplete, the fact that stored procs or prepared statements are part of that comes as no surprise to me. What this bug has just proven, is that not everyone is capable of writing an escape string. That doesn't change the fact that it's a zillion times cheaper to sanitize then call rather than call to prepare then call to execute, especially more so if you're not using stored porcedures and have to prepare / check if prepared.