Wednesday, November 5, 2008

MySQL, CFQUERY, and creating a user

Awhile ago I had worked on some code on our back end servers in which I needed to setup some databases through Coldfusion, and for security reasons I needed to set up a new user for each database that we created. So I went ahead and used CFEXECUTE and ran a nice little batch file each time I wanted to create a database. This worked excellent until we decided to run similar code on our production servers, in which the mysql server was seperate from the Coldfusion server. The goal was to create a create multiple databases in mysql and give each one their own username and password. Sounds easy, and, well, guess what? It is in Coldfusion!

One way of doing it, I found out from browsing around the internet, was to create a share over the network. I could create folders very easily for MyISAM databases (not so easily with Inodb), and mysql would recognize it as a new database. Setting up a user was a little more complex as I had to somehow create a batch file and then call it remotely. Now that's all fine, if you don't give a crap about security. You see, sharing files and opening ports is a big risk, especially if you are passing information that could grant a hacker access to all your mysql databases.

So I looked around the internet and I only found hints at a solution to my dilema, but it was enough to get an idea. The one example I did find was actually incorrect, and the person that had posted the question about why it was not working had not received an answer. So I decided to experiment, and I quickly found out how to accomplish what I needed to do: setup a database on a server within my network where the MySQL server wasn't on the same machine as the CF Server.

First, to create a database in Coldfusion using CFQUERY you need to have root access to your mysql database server. For those of you on shared hosting, you are out of luck because you aren't going to get that username and password! But if you have root access you can quickly create a database like this:

[cfquery name = "createdb" datasource="avalid_datasource" username="myrootuser" password="myrootpassword"]
CREATE DATABASE mydatabasename

Now of course, you would want to put some kind of error capture in around this or you will risk exposing your root username and password to someone if an error occurs and you have robust exemptions turned on.

Next, for security reasons mostly, I wanted to setup a username and password for each of my databases that I made. This is how I accomplished this:

[cfquery name = "createdb" datasource="avalid_datasource" username="myrootuser"]

Note that it is important you specify the root username and password in order to perform these actions or you will get an error.

Hope this helps someone out there! :-)

Tuesday, November 4, 2008

Version 5.3 is almost done!

Though I've been very busy lately with developing for Megalinx Corporation and other affiliated companies, I have been finding some time in recent weeks to get some bugs fixed and do a few more features in CFShopkart. Recently I added a little Classified Ads module for it so that will be included in the 5.3 release. It's nothing fancy, and most of you will probably not use it, but it's pretty cool and a good application to build of off if you are wanting to build something like it.

The account section is getting a little face lift and I have also redid the category logins (which I'm currently working on finishing up). You will be able to mark an item as a subscription item as before, but this time the persons cc info is encrypted in the database and saved for you to handle how you want. In this release I won't be supporting any payment processor recurring payment solutions, but that will come soon probably starting with and paypal (probably in a 5.3.x release I will add those in). For now it's just stored in the db and you can handle it how you need too (if anyone needs to use this feature). For now, I just plan to make it so that you can select which subscriptions get to access which categories. The reason I redid it, is I was working on something for someone else and I realized the way I had programed it just isn't very versitile, and this will make it much more versitile for future addons and modules.

I did some work on the themes to improve slightly on the implementation.

I am currently adding back in the ability to have downloadable goods! It's going to be good too, in that you will be able to attach multiple files to a product that your customers will then be able to download. Perfect for ebook sites or if you want to give them some type of download after checkout while they wait on a product to ship.

And I've been doing a lot of thinking on Version 6 already! Yes, even though 5.3 isn't quite out, I am thinking ahead :-)...version 6 will see some code changes in the admin. I am going to focus on making it more secure and faster. There are quite a few statement for the various components that will converted to statements. You'll notice that new features I've been adding use CFCASE instead of CFIF...sorry, but years ago when I first did this I used CFIF statements as that is what I learned, but then later learned about CFCASE and CFSWITCH which processes much quicker. So there will be some performance increases there.

I may in the meantime release a few more updates to 5, but I'm thinking that it's high time I quit adding more features, and just focus exclusively on bugs and adding payment processors.

EDIT: 02/2008 - Sorry guys, I've had to postpone the release. I'm doing some more work on it and have had a lot of work coming in. The release will be out soon though!

Welcome to the new CFShopkart Blog!

I have created a new blog for CFShopkart to keep you all up-to-date on what I'm doing and what is going on with CFShopkart. I will also post some updates and info as I have time. Thanks for stopping by!