fendale

Shameless plug. I've developed a similar tool to this without the gui frontend, distributed as a ruby gem - http://dbgeni.com It was inspired by Ruby on Rails and was quite a nice project to develop. It supports oracle, SQLite, sybase and MySQL out of the box. It also handles deploying stored procedures.

In a previous job, I used a technique like this to deploy pretty big Oracle application, being worked on by 160 developers, so using migrations to manage your database does work... with some care!

I followed through your profile, and the hiking app looks very good. Great job!

How do you get 'outdoor maps' into the App, ie do you license them or are they available freely somewhere?

Very useful. I'm currently on £15.50 O2 simplicity with no contract, and i'm weighing up paying Apple £600 directly for the unlocked phone on entering a new contract with O2. Looks like the cost of £600 up front and my current £15.50 a month is about the same total over 2 years at the £26 a month option for the 32gb phone... Only difference is the contract gives me way more voice minutes...

I've heard a few stories (usually on the Macbreak weekly podcast, never from anyone I know) of people going into the Apple store with a broken screen while under warranty having the phone replaced for free. It seems they have some license to do a good dead or two for customers, but there isn't an official policy. I guess with such a high margin product, they can be flexible sometimes.

> 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.

I admit I had not read the actual bug report before I commented, but I just did and it reads:

>Impacted code directly passes request params to the `where` method of an ActiveRecord class like this: Post.where(:id => params[:id]).all >An attacker can make a request that causes `params[:id]` to return a specially crafted hash that will cause the WHERE clause of the SQL statement to query an arbitrary table with some value.

For me that is a classic SQL injection attack that can be avoided 100% of the time if you use bind variables for all user input into an SQL query.

Rails doesn't use bind variables - it uses something that looks similar, but it is actually rails code that escapes and concatenates the user input into the query string before executing it. This bug was in that escaping code. However, if Rails correctly bound the inputs to all queries (which is pretty easy to do) then this escaping code could be removed totally and this problem would never have appeared.

Sigh - why oh why doesn't Rails use bind variables? Just about every DBMS I have used supports them (Sqlite, Mysql, Oracle, Sybase I know for sure) and if you correctly bind the variables into the query, fears about SQL Injection pretty much disappear.

Then there is performance - in Oracle bind variables are essential for scalability. I read somewhere that Mysql performs better without bind variables.

For most databases by caching prepared statement handles and binding / executing that handle many times, it will perform better than handling many one off queries. I have benchmarked a 20% improvement in throughput on Oracle by making just that change (which is a one liner in the JDBC driver).

Depending on how far north (or south) you live, daylight saving is arguably worth the hassle.

Where I live, in winter, without daylight saving it would be dark until 9.30am or after in the mornings, meaning all the kids need to go to school in the dark, which is more dangerous.

Then at the peak of summer, it would probably still be daylight until close to midnight.

The closer you are to the equator, the less swing there is between the number of hours of daylight between summer and winter. Even between the very north of the UK and the very south there is an appreciable difference.

What is a full join? I've been working with Oracle for 10 years and I've never used that term. I know what a left join is and an outer join, but not a full join.

Quite a few years ago, I setup tc to do a bit of traffic shaping on my home network. Instead of having a standard router, I have a Linux box with two network cards using iptables to do NAT. I connect my cable modem to one of the network cards and then I connect my home network to the other.

Way back, I found I had two problems - if I was doing something that did a lot of uploading (eg seeding a torrent), my download speed would tank because the tcp/ip packets were bogged down in the upload queue.

I also like to be able to ssh into my machine while away from home, and if I did that while uploads were going on, the ssh terminal was basically unusable.

Back then I discovered tc - it was able to make TCP control packets (ack's etc) and ssh related packets jump to the head of the queue, and the other upload stuff have a lower priority.

Looking back at the script I created now, I don't really understand what it is doing, but it remember it worked. I still run it today and just take it for granted.

I tested it by start a large upload while ssh'ed into the box from the internet - I turned off the tc rules and the terminal became unusably laggy, and then I turned the rules on and it worked perfectly.

Maybe doing it daily is too often to get started, certainly if you have a lot of other stuff going on in your life too.

I set myself a target of writing one good blog post a week about a year ago, with the target of having 52 posts in a year. Right now, I have about 31 with 1 month to go - I am pretty pleased with that, especially since I have been on at least 4 weeks holidays through the year where I left my laptop firmly at home.

Not really an SQL tool, but if you use a lot of SQL then maybe you need to apply DDL and procedures to various environments. I have been working on an installer to manage your DB schema using migration scripts - http://dbgeni.com

6162 does sound very like my issue. I must try sticking a sleep inside the block passed to popen and see if that gets past my problem. It's not a solution as such, as the process I am calling could take anywhere from a second to many minutes to execute, but it will be interesting to try. If you manage to solve these bugs it will be a great help to me, so good luck!

I'd love to help, but my Java skills are almost none existent! If I thought I could make any headway into that bug I'd jump in - maybe over the holidays I will try and have a look.

Until recently I dismissed JRuby, but then I needed to run some stress testing code against an Oracle DB. With MRI Ruby and the OCI8 library, the Ruby overhead stopped me being able to push the database hard enough. I switched to JRuby and JDBC and the results were amazing, so I am converted. The only niggle I have is the startup overhead, which I know is a JVM thing. Not a problem for my benchmarking, but a little bit of an annoyance for a command line ruby tool I use.

As an aside, I did run across a bug, not performance related, but a case around popen where JRuby doesn't behave the same as MRI - I raised a ticket (JRUBY-6225) but it doesn't seem to be getting any traction. I understand there are probably plenty of tickets to be prioritized, but this one is a bit of a show stopper for me. If anyone from the JRuby team is see this and has time to have a look at it I would be grateful.

When I was in the USA travelling and wanted to call someone on Skype back in the UK, I used the ring once hang up technique to signal for them to get logged onto Skype or text me back to say they cannot. When inthe USA on a UK mobile, it's about $1.50 per minute to make a call, and $0.50 to send a text. It's free to receive texts though so this hack let me communicate basically for free.

I was in SV for the first time recently. The google campus was cool, computer history musuem was also cool and almost beside google. It was good to see the Stanford campus too. SF was just like any other city for me, so I didn't do much sightseeing there.

What is the best way for someone who has been in a reasonably successful corporate job for about 10 years to get into this 'on the road' sort of consultancy. I'd love to do it, but the idea of just quitting work scares me - I've no doubt I am good enough to be a consultant in my area of expertise (which is Oracle databases), but knowing where to start is the issue. I do blog a bit, and have experimented with a few app ideas, but haven't managed to get anything serious off the ground as yet!

I think I read recently that using the Oracle (+) operator can give you the error "cannot outer join to more than one table" if you try to outer join to two or more, but the ANSI syntax will allow you to do that - I really should test this out ...

edit - I tried this out and right now cannot get that error, so not sure where I got that idea from.

I love that copy / paste method in putty. It drives me mad it doesn't work like that in the OS X terminal. Is there any way to get the OS X terminal to behave like Putty with copy and paste?

Pretty sure DabbleBD shut down because the company got acquired mainly to get the talent and not for the service. Cannot recall who acquired them.

I only ever played with it, but it seemed like a great piece of work, and was all coded in Smalltalk (Seaside).

Can mysql really not use an index to do a top / bottom 10 query? In Oracle it can do a top n query avoiding a sort very quickly on massive datasets given an index on the sort column. Being that is a common pattern in web apps, which is probably mysqls target market you'd think that would be high up the dev priority list!

Numbers or strings wasn't the point really. You can do 'order by 1' or 'order by 2' in SQL to order by the first or second selected col etc, but if you used used the number passed directly from the user in the SQL statement, you are open to SQL injection. Feel free to use the number in a case statement to select the order by string to concat into your SQL however.

Well, yes, but only when your data is skewed in general. Tom Kyte gives a 1 - 2 hour presentation about bind variables, bind variable peeking, overbinding, SQL Injection, parsing etc - great stuff if you are an Oracle guy and can get to one of this seminars.

> For SQL use prepared statements exclusively (never let "oh, it's just a number so I don't need to" fool you)

I cannot vote this up enough. Also, depending on what database you are using (eg Oracle) if you don't use prepared statements (aka bind variables) you are guarantee killing your DB performance.

People have argued with me in the past that for things like sorting the data they cannot use bind variables. In that case, use the user input to select which safe string to use, eg:

    if user_select_sort == 'by_account_num'
      return 'order by account_num asc'
    elsif user_select_sort == 'by_transaction_date'
      return 'order by transaction_date'
    else 
      return ''
    end if

Then if someone sends in something tricky, it will just order wrong.

You can certainly scale out with Oracle RAC. At some point the bottleneck will likely be disk however, so buying a high end storage system would probably become priority.

Surely it's not SAP (maxDB)? Never heard of it before I did some googling. Failing that the only DBs I can think of are Oracle, DB2, SQLServer and Sybase before going to the open source ones.

I'd also like to hear a reasonable answer to that question.

For me, the only advantages of stored Procs over straight SQL (generate or hand coded) are:

* Security - you can revoke rights to directly access / change the tables from the app layer, making all access be through procs. However, in general you can probably create a similar security setup using views.

* Many SQL calls that need to be run as a set to give a single answer - this avoids the round trip latency of firing many SQL calls from the application.

I'd am genuinely interested to hear of more advantages.

As another 'Oracle guy' this is an interesting post. I have said before on here, if you pay for Oracle, and also pay for decent storage arrays, Oracle can shift a serious amount of data before it reaches its limit. In my opinion, it really seems to be an order of magnitude better than its closest open source competitor.

Visa also now own authorize.net. They acquired them around the start of this year I think.