PHP Geek? Bored?

[lwood]

Among my several headaches responsibilities is a MySQL database and its JSP interface that are in extreme need of a rewrite. The pair of them belong to a 501c3 non-profit corporation, which means that I'm keenly interested in someone willing to work pro bono, and failing that to consider their time spent a tax-deductible donation.

The current JSP interface is fragile, very buggy, and the combination of the two is completely non-portable. Moreover, I don't want to install Apache Tomcat on my production server. Therefore, I would like it rewritten in PHP.

The database is in need of minor reconfiguration, although I have a DBA onboard to assist with this.

The database and its interface already have a primitive understanding of users and groups. This needs to be extended. Ideally, it needs to be able to provide password protection on a user and/or group basis for hierarch(ies) of static HTML files. Writing a page that's really PHP or JSP that consists of a big old include statment is not really okay. I am told that this rights management can be done exactly how I want it if we use Perl for this part, but really, I don't care as long as it talks to the database above, speaks unix-like users and groups, and can protect hierarchies of static data.

There's more, but that's the basics. If you're interested, or think you know of someone who is interested, please reply here or e-mail my username at LiveJournal.

Thanks!

-- Lorrie

Comments

[walkyrja.livejournal.com]

Hey there.

If you send this to me as an email I can post it to troth-new-york and torth-new-england to look for more recruits...

[lwood.livejournal.com]

Can do! You'll see it soon!

-- Lorrie

[slarti.livejournal.com]

I can perfectly well understand not wanting to install Tomcat. When I worked for WPI, we had to spank stupid CS students and the prof who wouldn't take "NO Tomcat on our servers" for an answer several times. (They'd install and run it themselves out of their web space, and we'd be like, "wtf is with our load? ... !!!! Naughty!")

Anyway, as to your particular problem... Is the aforementioned existing "interface" just a Web-based thing to administer the database content? Is the database just user/group names and passwords? Does the interface need to (or eventually to) do more than would be covered by PHPMyAdmin or whatever there is out there for existing Web db admin stuff?

Doing the "password protection on static HTML files" may just be a matter of installing and configuring mod_auth_mysql or whatever and instructing Apache appropriately on when to reference the database for authentication, much as with htpasswd files. No real scripting involved, unless I'm missing something.

As for porting the "interface", whatever in particular that is (not a whole lot of info here), and whatever besides that needs doing... I'd be willing to lend a hand. I've some PHP fu from having had to customize a Drupal (http://www.drupal.org/) installation for the website of a LARP I'm helping run (http://b5larp.e-space.gweep.net/), some Perl CGIw/DBI fu from personal projects not publicly available (though I can state that they involved much love of CGI::Application (http://search.cpan.org/~markstos/CGI-Application-3.22/lib/CGI/Application.pm) and HTML::Template (http://search.cpan.org/~samtregar/HTML-Template-2.7/Template.pm)), and the open free time of the unemployed. I'll admit to being more of a PostgreSQL (http://www.postgresql.org) fan than MySQL, though. :-)

[lwood.livejournal.com]

Anyway, as to your particular problem... Is the aforementioned existing "interface" just a Web-based thing to administer the database content? Is the database just user/group names and passwords? Does the interface need to (or eventually to) do more than would be covered by PHPMyAdmin or whatever there is out there for existing Web db admin stuff?

The database isn't just usernames and passwords. It's a several-table thingy that tracks:

usernames, passwords, and access levels (one table)
per-member data, e.g. name, e-mail, and phone number (another)
per-membership data: list of associated member records, mailing address, member expiration data (third)
Two more tables for tracking our regional representatives: one to track the people that cover the regions, and one to track the regions that the people cover

The web interface, as it is now, can do several specialized pre-set queries that are of use to several officers. Username/password/access make sure you only get access to the queries you're supposed to get access to, although right now the access its guarding is PHP pages. Data admins get simple forms by which they can alter existing records.

As the beast already has an internal user/pass checking routine, using standard http auth (which mod_mysql) provides means they'd have to essentially log in twice: once for standard auth, once for database internal auth, at least how things are currently arranged. Avoiding this double auth is what'd require script-fu.

I deliberately kept my level of detail low on the initial pass -- if you're willing to help now that you know more, that'd be great!

-- Lorrie

[slarti.livejournal.com]

The database isn't just usernames and passwords. It's a several-table thingy that tracks:

Okay, that makes sense. I figured there had to be additional data, but wasn't sure if it was relevant to the question what end-users of the site would/could see.

The web interface, as it is now, can do several specialized pre-set queries that are of use to several officers.

And that'll need porting from JSP to PHP or whatever. Okay.

[...] although right now the access its guarding is PHP pages.

In addition to or in place of the static HTML pages you want to protect? Or are they there for some other purpose?

Avoiding this double auth is what'd require script-fu.

So you'd want to make it so users only have to log in once and be able to access both the interface and the HTML pages? That's reasonable, yeah. Some form of server-level authentication is going to have to be involved somewhere, though, in order to keep people from going to pages they're not supposed to see, particularly since you've already nixed the "PHP script that does an include" method of providing access to the HTML files (and wisely so, IMO).

Looking a bit more through the Apache Module Registry (http://modules.apache.org/), I see there are at least a few modules that talk MySQL on the backend, cache authentication via cookies, and can redirect to a form page to ask for username/passwd if you don't have the right credentials. (This one (http://home.digithi.de/digithi/dev/mod_auth_cookie_mysql1.html), for instance, available for both Apache 1.3 and 2.0.) In porting the interface, one could make the interface's authentication use the same cookie and act accordingly. You'd also be able to have a proper logout capability (something regular HTTP auth is sadly lacking).

if you're willing to help now that you know more, that'd be great!

Yeah, sure. Like I said, I have time. :-)

[lwood.livejournal.com]

Okay, that makes sense. I figured there had to be additional data, but wasn't sure if it was relevant to the question what end-users of the site would/could see.

Well, it's not relevant insofar as the content is concerned, of course, but members should be able to edit their own member and membership data, e.g. mailing address. The last programmer never managed this...

And that'll need porting from JSP to PHP or whatever. Okay.

This, I'm told, will be fairly straightforward. Just write the querybits in PHP instead of JSP.

Something else that the last programmer never managed, though, was connection pooling: every query against the database takes forfuckingever because it has to query against every record in serial, there's no parallel processing. Connection pooling would provide some of this...

In addition to or in place of the static HTML pages you want to protect? Or are they there for some other purpose?

In addition to. The PHP stuff is there to display and manage the database in several ways. The HTML that we'd be protecting would be various members-only shinies, or things that we would only want people of a given clearance to download, etc.

So you'd want to make it so users only have to log in once and be able to access both the interface and the HTML pages? That's reasonable, yeah. Some form of server-level authentication is going to have to be involved somewhere, though, in order to keep people from going to pages they're not supposed to see, particularly since you've already nixed the "PHP script that does an include" method of providing access to the HTML files (and wisely so, IMO).

The current system uses matching server/client pairs of short-lived cookies to track successful authentications. If you haven't done anything on the server in an hour, poof, you must relog. So, yeah, server-level authentication happens, and must continue to do so. You'll have free access to the old source code; I bet several algorithms will be portable, once translated from one language to the other.

I'm not only a unix nerd of some experience, but even given that I'm anachronistic. I like my configuration files in text so I can beat on 'em with vi. My HTML? All in vi, and by the gods it's standards-compliant. And I am not turning JPEGs, PDFs, and gods-know-what-all into PHP files unless there's absolutely no other way. Ideally, having a subdirectory protected should protect all of its subdirectories, because not only am I a throwback, but I'm a lazy throwback, you know? Do work now to avoid later, yay.

The current code has a logout thingy which, at the least, deletes the serverside cookie.

Okay, let's get cracking -- e-mailing my LJ name at this domain will get to me, and then we can get started. I'm so excited!

-- Lorrie