[grouch]

I'm going to be rich. For mental anguish, suffering, induced paranoia and trauma, my lawyers will get in line with all the others going after Jon's mountains of gold.

(Ok, this is barely on-topic for this forum, but I have to squawk about it somewhere. Also, it's about what will hopefully become the basis of the new journal software and so any suggestions or help from Hotrodders.com members should come out here.)

I broke everything about 'journals_test' (http://www.crankshaftcoalition.com/f...&page=11&pp=10) while trying to eliminate places where SQL injection attacks could take place. Jon is the one who mentioned such attacks and is thus responsible for my paranoia and suffering resulting from a Google search to find out how those happen. It's amazing how many trivial ways attackers can take over databases through "web apps".

The latest breakage took a marathon of head-banging to fix. My idiotic, Frankensteinian mess kept naming all uploaded photos by the journal ID, saving them in the wrong place, not updating the photos table in the database, and being smug about it by not reporting any error at all. After a day, 2 all-nighters, and creating 32 broken or empty test journals, it remains smug, but functions as it should. (The worst was caused by the differences between MySQL's LAST_INSERT_ID and PostgreSQL's currval('[sequence name]').)

Things that are working:
- multiple journals per user
- a summary entry for each journal, with 1 photo
- a table of contents per journal, with thumbnails
- 0 to 6 photos per entry, with thumbnails
- paging of journal summaries
- sorting by journalid, userid, date, entries, views, etc.

Things still broken:
-- adding an entry
-- editing an entry
-- formatting user input

As soon as those work again, I'll upload the whole mess to the Crankshaft Coalition thread linked above. A limited test of it will then be set up on my own website.

Feedback and help are welcome.

Back to breaking things...

[Rambo_The_Dog]

I'm a SQLServer/windows/web programmer - don't know MySql at all...seems a lot of your problems could have been solved using a transaction space to manage inserts/updates across tables...also I'd guess you aren't using RI to inforce relationships? - otherwise you should have gotten an error...but again I come from SQL Server background and maybe MySql isn't as robust?

[grouch]

None of your assumptions have anything whatsoever to do with the problems described. That includes the rather bizarre notion of "robust" applying to MS SQLS. You might want to think about why Microsoft prohibits publication of benchmark comparisons, to the point of legal threats.

I think this thread is already dead, which means I should not have started it in the first place.

[Rambo_The_Dog]

Originally Posted by grouch

None of your assumptions have anything whatsoever to do with the problems described. That includes the rather bizarre notion of "robust" applying to MS SQLS. You might want to think about why Microsoft prohibits publication of benchmark comparisons, to the point of legal threats.

I think this thread is already dead, which means I should not have started it in the first place.

It wasn't my intent to 'belittle' my sql - I have never researched it or used it - but you obviously have an bias against MS products which I've used for over 11 years and made tons-o-money programming against...maybe you are someone who thinks programmers shouldn't make a living?

Asking someone for their help or opinion and then telling me that my "None of your assumptions have anything whatsoever to do with the problems described..." is not only wrong based on your description of the problem it's disrespectful and belligerent - I doubt I'll take any time to answer another of your posts...

[grouch]

Ok, these allegations require a response.

Originally Posted by Rambo_The_Dog

It wasn't my intent to 'belittle' my sql - I have never researched it or used it

Yet you did exactly that, with your "robust" comment, even though nothing in my opening comment mentioned any shortcoming of MySQL.

Originally Posted by Rambo_The_Dog

- but you obviously have an bias against MS products

Absolutely true. My bias is not an unreasonable one, rather it is a reasoned and reasonable bias based on the facts regarding Microsoft's behavior over the course of over two decades. Microsoft uses its products as weapons against any business which dares intrude on turf MS has staked out for its own. I tend to harbor bias against criminal activity because such criminal activity imposes costs on me and everyone else.

Originally Posted by Rambo_The_Dog

which I've used for over 11 years and made tons-o-money programming against...maybe you are someone who thinks programmers shouldn't make a living?

Nice non-sequitur and red herring you toss out there. There are more programmers making a living without MS SQL, but that has nothing to do with the journal software nor the problems I described.

Originally Posted by Rambo_The_Dog

Asking someone for their help or opinion and then telling me that my "None of your assumptions have anything whatsoever to do with the problems described..." is not only wrong based on your description of the problem it's disrespectful and belligerent - I doubt I'll take any time to answer another of your posts...

Let's take a look at your response, in detail, and see what "help or opinion" was given.

Originally Posted by Rambo_The_Dog

I'm a SQLServer/windows/web programmer

This is a good opener that provides background. I've known programmers who can deal with standards, such as SQL, in spite of their day job requiring that they work with MS products.

Originally Posted by Rambo_The_Dog

- don't know MySql at all...

Nothing wrong here, either; maybe a bit surprising given how ubiquitous MySQL is on the Web, but still not a show-stopper.

Originally Posted by Rambo_The_Dog

seems a lot of your problems could have been solved using a transaction space to manage inserts/updates across tables

Here's where things start downhill. My buzzword meter starting dancing like crazy at this point. Transactions have nothing at all to do with misnamed file uploads. Using transactions would not have altered the incorrect behavior of my program in any way. If I were handling online banking, I would certainly base it on transactions, so that rollbacks could take place if there were any interruption in the connection.

Originally Posted by Rambo_The_Dog

...also I'd guess you aren't using RI to inforce relationships? - otherwise you should have gotten an error

Assuming "RI" means "referential integrity", a.k.a. foreign keys, no, it is not being used, but using it would not have resulted in the generation of an error. The fact that no error was generated, the photos table was not updated, the image files were being misnamed and being uploaded to the wrong place are, combined, a pretty bright red flag indicating a flaw in the logic of the program. In other words, _my_ screw-up.

Originally Posted by Rambo_The_Dog

...but again I come from SQL Server background and maybe MySql isn't as robust?

This is the kicker. It pegged the needle. Try this hypothetical situation on for size: Let's say I posted a description of installing the distributor in some Dodge engine, 180 degrees wrong. The first response comes from someone who says they've never worked on a Dodge, but it looks like my problems would have been solved if I had plugged the vacuum advance and reprogrammed the ECM. Oh, but maybe Ford systems are just more robust than those Mopars.

Now, to top it all off, MySQL was mentioned once:

Originally Posted by grouch

(The worst was caused by the differences between MySQL's LAST_INSERT_ID and PostgreSQL's currval('[sequence name]').)

Granted, that's pretty cryptic, but nowhere does it indicate a failing of MySQL. In fact, I wasn't using MySQL at the time of the problems described. I was using PostgreSQL in order to work out how to make the software work the same with both database managers. The results would be the same if I had been using Oracle or anything else: the flawed logic in what I had written would have resulted in misnamed file uploads being stored in the wrong place, no update to the database table I expected to be updated, and no error generated by the dbms.

To sum up:
1. Transactions do not prevent flawed logic.
2. Referential integrity checking does not prevent flawed logic.
3. One can mash a thumb with any brand of hammer.

Back to MS SQLServer, the scariest accounts of SQL injection attacks that I found were associated with that product. It seems that when MSSQLS is compromised by user input such as 1' OR '1'='1', it's not just the database than can be taken over. The server can be instructed to silently redirect so that users continue to think everything is fine, yet their data is going straight to the attacker's server. Just another "feature" that comes from tying "applications" too tightly with the underlying operating system.

The fact that the journal software needs to be multi-platform and freely alterable by sysadmins pretty well excludes MS products. MS is the spoiled brat of computing -- they must have everything to themselves and done their way or they won't play.

Now, after suitable time for you to respond if you so desire, I hope that some moderator will be merciful and send this thread to the dump. It does not include suggestions for inclusion in the new journal software, it does not include any help for making the new journal software more secure and it likely has zero chance of acquiring any information along those lines.

[NDNslicks4me]

http://www.spidynamics.com/whitepape...LInjection.pdf

Dead Ends
There are situations that you may not be able to defeat without an enormous
amount of effort, if at all. Occasionally you’ll find yourself in a query that you
just can’t seem to break. No matter what you do, you get error after error
after error. Many times, this is because you’re trapped inside a function
that’s inside a WHERE clause, and the WHERE clause is in a subselect which is an
argument of another function whose output is having string manipulations
performed on it and then used in a LIKE clause which is in a subselect
somewhere else. Not even SQL Server’s “;- -” can rescue you in those
cases.

[grouch]

Originally Posted by NDNslicks4me

http://www.spidynamics.com/whitepape...LInjection.pdf

Ok, so I was wrong about there being zero chance of this thread generating useful information.

Thanks for that link. The PDF is heavily focused on MS SQL Server and Oracle, and a bit light on solutions, but it contains good examples of techniques. The section on testing is particularly helpful.

All of my database work has been in protected situations where no outside connection to the database server existed. It didn't matter if a query returned more information than necessary because the only people able to submit a query were already trusted with all data.

I've never fallen into a "dead end" like is described in the part you quoted, but I did have a query (not in the journals test) that included over 300 "union all" clauses. That's over 300 subexpressions where such a dead end loop could have happened. It's as nerve wracking as finishing an engine rebuild and wondering where the air filter wingnut went.