> battery-backed write caches I have a laptop as well;-) I don't think you meant that. It's probably more cost efficient to use two cheap computers than one really expensive one. That means, if you can't afford to lose transactions, you should use clustering. With clustering, asynchronous commits are not a problem.
> that you didn't properly setup to have durability
Theoretically you are right, but it just doesn't work. You can change the BIOS settings, you can disable the write cache in the OS, and you can call fsync(). Most hard drives will ignore all that. You need to run tests to be sure.
You never _had_ durability. On most system. See http://hardware.slashdot.org/article.pl?sid=05/05/13/0529252. Durability is hard - mainly because of hard drives. See also http://www.h2database.com/html/advanced.html#durability_problems (I wrote that). It's not about 'losing data randomly', it's about losing transactions. The risk is: if there is a power failure or the process is killed, you may lose the transactions of the last x milliseconds. In most cases, you wouldn't know if the commit call returned before the failure, and for for those cases where it's important (distributed transactions), you anyway need the 2-phase-commit protocol. And again, this is not about corruption or losing records randomly, it's a about transactions.
> your statement that your database is the only one that supports parameterized queries
I didn't write that. I wrote: "force the usage of parameters". Probably I should have written "enforce using parameters". 'Enforce using parameters' and 'support parameters' is not the same. H2 is the only database that can enforce using parameters.
> ROFL Why is that funny? If all database engines would support this feature, the world would be safer!... and I would be rich because I invented it? No: it is not patented.
all database servers (including SQL Server I'm sure) does offer a semi-manual form of protection... prepare a query and put parameters in it... Application authors just need to use this feature... Developers just don't do that always, because they forget it (or ignore the problem). There is a solution: force the usage of parameters. So far only one database supports this feature: the H2 Database Engine. For more information, see: URL:http://www.h2database.com/html/advanced.html#sql_injection
- Custom code solving my problem (not 'general purpose' yet) - Text starting with 'abc' is ignored (for random test data) - Some files are ignored (Japanese documentation for example) - There is one word list, you have to extend it yourself - Checks all files in a directory and all subdirectories - Some file types are ignored (unknown file types are listed) - Some directories (SVN, CSV) are ignored - CamelCase and UPPER_CASE: each word is checked individually - Java, quite fast, but not optimized yet - Just one class (plus one utilities), no other dependencies
Hi,
I made one and use it for my open source Java database. It is very simple so far, based on a word list. Supports camel case and so on. It is here: H2 Database Engine, src/tools/org/h2/tools/doc/SpellChecker.java. Or here: SpellChecker.java. It can also check XML, HTML, JSP,... Words shorter than 2 or less characters are ignored. If you want to spin off you own project go ahead, I can help you.
I have included it in the build script: Whenever you write more than a few lines of new code (or documentation) the spell checker will bark because it doesn't know the word. Maybe I should add an automated 'word list expander' that checks unknown words on the internet... Anyway, the hard part will be to convince your coworkers to use it.
For 1-D people that live in an expanding 2-D ring, everywhere is the center. For 2-D people that live on an expanding 3-D ballon, everywhere is the middle. We 3-D people live in an expanding 4-D universe, for us everywhere is the middle.
That means if you go forever in the same direction, you will eventually end up where you started.
> Your idea makes coding that a lot more annoying.
Yes, it does. Unfortunately. But I don't think it would be that bad, many problems could be solved using views. And maybe database defined constants (like using constants in a regular programming language, anyway a good idea):
SELECT... FROM users where username=? and userlevel in (CONST.ADMIN, CONST.MODERATOR)
You would need to define the constants in the database somehow (not sure if there is a SQL standard). You would need to do that only once:
SET CONST.ADMIN = 'admin'; SET CONST.MODERATOR = 'moderator';
Reminds me, I need to implement constants in my H2 database engine as well...
stmt.executeQuery("SELECT * FROM users WHERE name='" + name +"'");
Then the database engine would throw the following exception:
Text literal not allowed in SQL statement: SELECT * FROM users WHERE something='';DROP TABLE users; SELECT * FROM othertable WHERE name=''
No matter what is in 'name' (that means even if name="Test"). Because it would detect that the SQL statement contains one (or more) text literals. So you would find insecure programming early in development. But if you do this:
PreparedStatement prep; prep = conn.prepareStatement("SELECT * FROM users WHERE name=?"); prep.setString(1, name);
Then it would work. Because there are no text literals, only a parameter. And you can't do SQL injection with parameters. Magic, isn't it? I'm not sure how to do that with other languages than Java / JDBC, but I'm sure it's possible.
Replying to myself... Not that you guys think 'but I will not migrate to your stupid H2 database'. This could be done in a small JDBC (or whatever) driver that sits between the 'real' database driver and the application. This driver would just relay all calls to the underlying driver. Except, it would check for literals in SQL statements.
I write this partially because I don't want that anybody patents this idea - Does posting in Slashdot create prior art?
Many developers write code like execute("SELECT... WHERE NAME='"+name+"'...) because it's so easy, they are lazy, or because they are clueless. Many know that they should use bind variables, but not all (and peer reviews are not very common).
There is a way to solve SQL injection problems: Disallow text literals in the database engine. Or even, disallow literals (including numbers) at all. This could be a setting in the database that is on by default, and only off for certain applications (ad hoc query tools). What do you think about that?
SET ALLOW_LITERALS 0 (no literals allowed) SET ALLOW_LITERALS 1 (only numbers, text not) SET ALLOW_LITERALS 2 (everything allowed)
This would be a persistent setting, and only an admin can change it.
(Of course there are other security risks, like using 'customer id' in URL or hidden fields in a web application. Or relying on Javascript data validation. I don't know what to do about those problems.)
I know it's completely off-topic but...
'what you took is in there somewhere, figure it out.' (SCO)
'We know they (WMD) are in that area' (Rumsfeld)
SCO just hasn't made its case against IBM (Judge)
Bush hasn't made it's case (public opinion in Europe)
would just encourage people to start using numeric encoding in places where it shouldn't be kept.
I saw lots of that already. Views with embedded constants (... WHERE FORM_TYPE = 5000001025) for example. Reminds me to add support for constants in the database engine. I wonder if other databases support constants, and if there is a standard for that.
Use two single quotes ('') to create a quote. Does that imply that this will happen even when using prepared statements?
No, just when using it in a query directly. Data that is set via PreparedStatement.setX is not parsed. I think all SQL database work like that. The two single quotes is standard SQL, but there are some databases (PostgreSQL? I forgot) who supports a backslash as an escape character, but this is non-standard.
Obviously, this is unsafe. I even wrote such code myself (baaaad). The problem is, many developers don't know how unsafe it is. Most know that they should use PreparedStatement, but don't do it for one reason (mostly laziness) or the other (preparing statements is slow in Oracle, index not used for 'LIKE ?' in some databases).
There is a way to solve SQL injection problems: Disallow text literals. Or even, disallow literals (including numbers) at all. This could be a setting in the database that is on by default, and only off for certain applications (ad hoc query tools) or users (admins). What do you think about that?
SET ALLOW_LITERALS 0 (no literals allowed) SET ALLOW_LITERALS 1 (only numbers, text not) SET ALLOW_LITERALS 2 (everything allowed)
This would be a persistent setting, and only an admin can change it. But, maybe this is the wrong place to ask for comments on this?
(Of course there are other security risks, like using 'customer id' in URL or hidden fields in a web application. Or relying on Javascript data validation. But I don't know what to do about those problems.)
Re:SQL is a standard. Is it?
on
SQL Cookbook
·
· Score: 1
> you didn't know that SQL is an ANSI standard? Of course I did.
> But do not claim we need another query language No. All we need is a _good_ standard. A subset without variations. Maybe with a reference implementation.
> Force the venders to comply. The problem is, the standard is so huge it's impossible to comply. Again, a _good_ standard would be simpler, smaller, and it would be possible to comply. Like the Java language (I'm not saying this is a perfect example, see Java compiler problems, but it's at least better than SQL).
Re:SQL is a standard. Is it?
on
SQL Cookbook
·
· Score: 1
Yes, exactly. There are so many fundamental differences in the products that call themselves 'standard compliant' that it's not funny. Examples:
In Oracle, NULL is the same as an empty string (''). Data types are named differently for each database vendor. String concatenation is different in SQL Server. CREATE INDEX is not defined in the standard (but most databases support it). Some databases support sequences, some autoincrement columns. Schema and catalog behaviour is different. Date constants are different. SQL Exceptions are different. Dealing with BLOBs is different in most databases. MySQL used to (not sure if it still does) truncate empty spaces at the end of VARCHARs. PostgreSQL: after an exception occured, it is required to issue a commit or rollback (is this fixed?).
Re:SQL is a standard. Is it?
on
SQL Cookbook
·
· Score: 1
What does a standard help if nobody follows it?
> The standard itself is fully documented.
On how many pages? Maybe the size of the standard is the problem.
> Blame the developers, not ANSI.
I'm not blaming the developers, as it's quite impossible to implement this huge standard. I don't blame the commitee. They did what such a group usually does: they held meetings. Lots of them.
Still, there is no standard. Otherwise, how do you explain having to list 5 dialects?
SQL is a standard. Is it?
on
SQL Cookbook
·
· Score: 1, Informative
What I have always found funny about SQL is that, while it's very 'old' (in software terms), and mature, and widely used, there is in fact no real standard. There never was. From the article:
SQL variations... While SQL is a standard, there seems to be some very relaxed definitions of full adherence to that standard...
Or, as Jim Starkey said: 'SQL isn't a standard but a theme'. For a book, it means list 5 different dialects. For regular developers (not database specialists) it means knowing only one dialect really well. For an application it means, running only with one database (mostly). It would be really cool the industry could get together and define a 'real' standard. Could be a subset of SQL (http://ldbc.sf.net/) or a new language (http://newsql.sf.net/). Things would get simpler then.
(Side note: LDBC and NewSQL are both projects I started, but interest was quite low; currently I'm working on a new database engine http://www.h2database.com/ where I try to be compatible as much as possible with existing databases)
Or is there some other solution? I don't think that that O/R mapping tools will solve the problem completely, as there is always the need interactive database queries. Maybe the Microsoft extension to C# (forgot the name) could be a solution? Other ideas?
"Free use for those who never copy, modify or distribute. As long as you never distribute the MySQL Software in any way, you are free to use it for powering your application, irrespective of whether your application is under GPL license or not." http://www.mysql.com/company/legal/licensing/opens ource-license.html
So if you ship your (commercial) application with (the unmodified) MySQL, you need to use the commercial license. I don't think this is so with Linux, because the important libraries (LIBC) are LGPL and not GPL. Also, each part of the system can have a different license in Linux. Not so if you use MySQL (see above). At least this is my understanding, please tell me (with the relevant links) if you think I'm wrong. -- http://www.h2database.com/
Slashdot Mirror
> that you didn't properly setup to have durability Theoretically you are right, but it just doesn't work. You can change the BIOS settings, you can disable the write cache in the OS, and you can call fsync(). Most hard drives will ignore all that. You need to run tests to be sure.
You never _had_ durability. On most system. See http://hardware.slashdot.org/article.pl?sid=05/05/13/0529252. Durability is hard - mainly because of hard drives. See also http://www.h2database.com/html/advanced.html#durability_problems (I wrote that). It's not about 'losing data randomly', it's about losing transactions. The risk is: if there is a power failure or the process is killed, you may lose the transactions of the last x milliseconds. In most cases, you wouldn't know if the commit call returned before the failure, and for for those cases where it's important (distributed transactions), you anyway need the 2-phase-commit protocol. And again, this is not about corruption or losing records randomly, it's a about transactions.
There is a missunderstanding.
> your statement that your database is the only one that supports parameterized queries
I didn't write that. I wrote: "force the usage of parameters". Probably I should have written "enforce using parameters". 'Enforce using parameters' and 'support parameters' is not the same. H2 is the only database that can enforce using parameters.
> ROFL ... and I would be rich because I invented it? No: it is not patented.
Why is that funny? If all database engines would support this feature, the world would be safer!
> you are the author of the database you are pimping here?
Yes. Sorry I should have said that in the post.
Some more details about my own spell checker:
- Custom code solving my problem (not 'general purpose' yet)
- Text starting with 'abc' is ignored (for random test data)
- Some files are ignored (Japanese documentation for example)
- There is one word list, you have to extend it yourself
- Checks all files in a directory and all subdirectories
- Some file types are ignored (unknown file types are listed)
- Some directories (SVN, CSV) are ignored
- CamelCase and UPPER_CASE: each word is checked individually
- Java, quite fast, but not optimized yet
- Just one class (plus one utilities), no other dependencies
I hope this helps,
Thomas
I made one and use it for my open source Java database. It is very simple so far, based on a word list. Supports camel case and so on. It is here: H2 Database Engine, src/tools/org/h2/tools/doc/SpellChecker.java. Or here: SpellChecker.java. It can also check XML, HTML, JSP,... Words shorter than 2 or less characters are ignored. If you want to spin off you own project go ahead, I can help you.
I have included it in the build script: Whenever you write more than a few lines of new code (or documentation) the spell checker will bark because it doesn't know the word. Maybe I should add an automated 'word list expander' that checks unknown words on the internet... Anyway, the hard part will be to convince your coworkers to use it.
For 1-D people that live in an expanding 2-D ring, everywhere is the center.
For 2-D people that live on an expanding 3-D ballon, everywhere is the middle.
We 3-D people live in an expanding 4-D universe, for us everywhere is the middle.
That means if you go forever in the same direction,
you will eventually end up where you started.
A successor to SQL - NewSQL: http://newsql.sf.net/
The original article (with some more information, but in German) is here: http://www.ethlife.ethz.ch/articles/neuerantrieb.h tml
Yes, it does. Unfortunately. But I don't think it would be that bad, many problems could be solved using views. And maybe database defined constants (like using constants in a regular programming language, anyway a good idea):
You would need to define the constants in the database somehow (not sure if there is a SQL standard). You would need to do that only once:Reminds me, I need to implement constants in my H2 database engine as well...I write this partially because I don't want that anybody patents this idea - Does posting in Slashdot create prior art?
There is a way to solve SQL injection problems: Disallow text literals in the database engine. Or even, disallow literals (including numbers) at all. This could be a setting in the database that is on by default, and only off for certain applications (ad hoc query tools). What do you think about that?
I'm thinking about implementing this feature in the database I write (http://www.h2database.com/):
This would be a persistent setting, and only an admin can change it.(Of course there are other security risks, like using 'customer id' in URL or hidden fields in a web application. Or relying on Javascript data validation. I don't know what to do about those problems.)
I know it's completely off-topic but... 'what you took is in there somewhere, figure it out.' (SCO) 'We know they (WMD) are in that area' (Rumsfeld) SCO just hasn't made its case against IBM (Judge) Bush hasn't made it's case (public opinion in Europe)
There is a way to solve SQL injection problems: Disallow text literals. Or even, disallow literals (including numbers) at all. This could be a setting in the database that is on by default, and only off for certain applications (ad hoc query tools) or users (admins). What do you think about that?
I'm thinking about implementing this feature in the database I write (http://www.h2database.com/):
This would be a persistent setting, and only an admin can change it. But, maybe this is the wrong place to ask for comments on this?(Of course there are other security risks, like using 'customer id' in URL or hidden fields in a web application. Or relying on Javascript data validation. But I don't know what to do about those problems.)
> you didn't know that SQL is an ANSI standard?
Of course I did.
> But do not claim we need another query language
No. All we need is a _good_ standard. A subset without variations. Maybe with a reference implementation.
> Force the venders to comply.
The problem is, the standard is so huge it's impossible to comply. Again, a _good_ standard would be simpler, smaller, and it would be possible to comply. Like the Java language (I'm not saying this is a perfect example, see Java compiler problems, but it's at least better than SQL).
In Oracle, NULL is the same as an empty string (''). Data types are named differently for each database vendor. String concatenation is different in SQL Server. CREATE INDEX is not defined in the standard (but most databases support it). Some databases support sequences, some autoincrement columns. Schema and catalog behaviour is different. Date constants are different. SQL Exceptions are different. Dealing with BLOBs is different in most databases. MySQL used to (not sure if it still does) truncate empty spaces at the end of VARCHARs. PostgreSQL: after an exception occured, it is required to issue a commit or rollback (is this fixed?).
What does a standard help if nobody follows it?
> The standard itself is fully documented.
On how many pages? Maybe the size of the standard is the problem.
> Blame the developers, not ANSI.
I'm not blaming the developers, as it's quite impossible to implement this huge standard. I don't blame the commitee. They did what such a group usually does: they held meetings. Lots of them.
Still, there is no standard. Otherwise, how do you explain having to list 5 dialects?
SQL variations ... While SQL is a standard, there seems to be some very relaxed definitions of full adherence to that standard...
Or, as Jim Starkey said: 'SQL isn't a standard but a theme'. For a book, it means list 5 different dialects. For regular developers (not database specialists) it means knowing only one dialect really well. For an application it means, running only with one database (mostly). It would be really cool the industry could get together and define a 'real' standard. Could be a subset of SQL (http://ldbc.sf.net/) or a new language (http://newsql.sf.net/). Things would get simpler then.
(Side note: LDBC and NewSQL are both projects I started, but interest was quite low; currently I'm working on a new database engine http://www.h2database.com/ where I try to be compatible as much as possible with existing databases)
Or is there some other solution? I don't think that that O/R mapping tools will solve the problem completely, as there is always the need interactive database queries. Maybe the Microsoft extension to C# (forgot the name) could be a solution? Other ideas?
It's not so clear if MySQL is 'free for commercial use' or not:
... for organizations that do not want to release their application source code."e rcial-license.html
s ource-license.html
"The Commercial License
http://www.mysql.com/company/legal/licensing/comm
"Free use for those who never copy, modify or distribute. As long as you never distribute the MySQL Software in any way, you are free to use it for powering your application, irrespective of whether your application is under GPL license or not."
http://www.mysql.com/company/legal/licensing/open
So if you ship your (commercial) application with (the unmodified) MySQL, you need to use the commercial license. I don't think this is so with Linux, because the important libraries (LIBC) are LGPL and not GPL. Also, each part of the system can have a different license in Linux. Not so if you use MySQL (see above). At least this is my understanding, please tell me (with the relevant links) if you think I'm wrong.
--
http://www.h2database.com/