| |
Klixxx Magazine Archive - Tech Tips |
|
|
The Portable Database
by Tim Schaefer
Last time I wrote about a feature of Perl that you can use to set up Perl to behave just like a web browser in your Perl program, using the LWP modules. This time around I want to extend that concept and show you the power of LWP again, using it for database connectivity with your website. I also want to talk to you about building your membership database with the MySQL database engine on Linux. If you aren't familiar with this little gem of software you should be. When you combine the two, Perl and MySQL, you have just about everything you need to building scalable databases for your websites.
What is MySQL? MySQL is a relatively free database product available for both Linux and NT. Make no mistake about the "free" part, this database engine is a screamer. MySQL is lean, fast, and very robust. You can learn more about it at www.mysql.com. I highly recommend using a Linux server, it is easier to work with than NT when considering Perl, a lot more flexible, and a lot cheaper than using NT.
First off, what I'm writing this time around about can be used for both large website owners as well as the little guy starting out. Being scalable is important because nobody I've ever talked to wants to stay small, and the big guys like getting bigger. Using Perl and MySQL is a great way to add scalability to your website because both are simple and robust, two key components to building a great system for large and small systems. As of this writing LWP is still not available for PHP, but perhaps there is still a way for you to adapt PHP to what we're talking about here.
One of the things you'll find out if you're just starting out is the myriad of choices available to you for hosting your site. Not all hosting companies support a database engine, those that do often do not provide the expert kind of support you will need for programming your database with your website. Those that do offer database support often charge you more for the database connectivity and support.
But even if the cost is not an issue, perhaps the idea that all that data in the database is not totally secure unless you know who has access to the box. After all, if it's not sitting where you can control access to the machine that runs the database, is it really secure?
If you are using a database server on somebody else's server that you are renting do you really know what is being done in the dark of night with that data unless you have total security to the server? The only way to have total security to the data in the database is to make sure the database server is in your shop, in YOUR range of vision. There are several other advantages to this but for now total security is the number one reason you should separate the database from the webserver. You have complete control over who accesses that data.
Having said all that, I do not want to imply that the ISP or hosting company you are using would compromise your system. But I am considering that unless you can see the box, and monitor who is accessing it, the security to the system is as good as that. So the question is, if the box shouldn't be at the hosting company, how do I still maintain connectivity to the database between my shop and the hosting company? Good question.
Let's take a look at setting up a database server with MySQL. I've found that by setting up the database server as yet another webserver, it provides a portable approach to your database. As long as your website can find the database server, you can keep the two separate. The fundamental difference in the approach using LWP is that it allows you to further insulate your database security by not exposing database passwords on your hosted server. This way nobody can see how to get into your database. MySQL requires some kind of database user id and password in order to connect to the database, but in using LWP you can hide these parameters completely using LWP.
So, you set up the database server to run on a Linux box, and CGI programs on the database server send and receive data from your websites. Using this method can allow you to spend less money up front on a web site, eliminating the need for a database cost at the hosting company, and setting up a more secure database in the process.
I've included a sample Perl code snippet to illustrate how you would use the system we're talking about. The following example is code you would use on your webserver to send signup information to your database server without exposing passwords to the database:
The above code is used inside your signup program on your hosted website. Nobody except the hosting company can get to it if your /cgi-bin directory is set up properly on your site server. Of course if you are the only one with access to this server it's even better. This means that your hosting company should be the only ones able to get to this code besides you, and if you are paying for total and exclusive access to the box you should be the only one able to see this code. All you're doing at this point is sending data to your database. Once you send the data quietly to your database server, your database server will send you a little message back that it put the data into the database.
Remember to have LWP set up in your Perl program on the website. This is purely an example, what fields to send are of course up to you, and you might even include an additional field that only you would know about to prevent outsiders from trying to hack your system. Conspicuously absent in the above example: passwords to the database.
Over on the database server you'll have a program to insert the data. Again just a code snippet. You'll have to write the rest of the program:
The above example shows that the database server is going to print something out, back to the LWP function on the host server. At this point on the host server you'll tell your member that they had a successful signup or tell them there was an error.
So between the two servers you can pass information back and forth about your members, and maintain some security. To authenticate members logging into your site once they sign up you will have yet another CGI program on your database server to look up a member based on certain criteria used when they sign up. This is usually some kind of username and password.
Of course you need to make sure for ultimate security not to pass information back and forth that can be intercepted and misused. One of the ways to do this is to send information in such a way that it doesn't mean anything unless you know how to assemble it and use it. Don't send credit card data at all!! Also any privacy-sensitive information could be sent encrypted, then decrypted on the database server. Anything else that would provide outsiders with an opportunity to steal your information should be sent as secure as possible.
Advantages outweigh disadvantages to doing things this way especially for smaller shops where the infrastructure for the website will probably be changing and be more unstable than the database. Since the database is separate from the website you can make changes to the website and the webserver for the website without impacting the database server. Plus you can use one database for several websites.
MySQL is a great database for websites, and many of you webmasters are already using it. If you are new to MySQL you should check out phpMyAdmin, a tool you can and should use to manage your database. This too is free and available at www.phpwizard.net/projects/phpMyAdmin/. It takes about 5 minutes to set up and can save you a lot of time when administering your databases. There are several other client tools to administer MySQL, but as far as I'm concerned this one is the best. You can find a bunch more over at www.mysql.com/downloads/contrib.html.
|
|
|
|
|
