Friday, December 5, 2008

Running MySQL queries from your browser

Download the code. You will also need to download the Connector/Net binaries.

phpMyAdmin has a place for you to run MySQL queries but there are a few reasons you might want to have a your own admin page on your site for running ad hoc queries.
  1. You might want your queries to run under a different user than the one used by phpMyAdmin. See here for why you may need to run your stored procedure creation scripts this way.
  2. You might want to test a query going through Connector/Net.
  3. Maybe you just want a way to run queries faster without having to log in.
Here is a snippet from a simple page I created to run MySQL queries against my site.
protected void btnSubmit_OnClick(object sender, System.EventArgs e) {
  if (txtKey.Text == "some random string") {
    StringBuilder sb = new StringBuilder();
    using (MySqlConnection conn = new MySqlConnection("Server=localhost; Port=3306; ...")) {
      MySqlCommand cmd = new MySqlCommand(txtSql.Text, conn);
      MySqlDataReader rdr = cmd.ExecuteReader();
      while (...) {
        sb.Append(results of query);
    litOutput.Text = sb.ToString();
Download the real code

Security considerations
  1. Remember to put in a random string for your key in the page.
  2. You may want to remove or disable the page when you are not using it.
  3. Use ssl encryption whenever you visit the page. If you don't have an ssl certificate for your site you can create a self signed certificate in cPanel if your hosting provider lets you. Mine lets me create and import certificates but they don't let me turn on ssl for my site unless I pay for an upgrade so I cannot actually do anything with them. A self signed certificate will give you a warning in your browser but the transmission will still be encrypted. If you are like me and can't enable ssl for your site another option is to encrypt the data in javascrypt before sending it to your server side code.
    Download source code of a version that uses encryption.

1 comment:

Anonymous said...

I know this comment may be a little late but I had to post it anyway.

Thank you very much, I was stuck with this problem and couldn't find a solution for it anywhere even with the technical support until I found this ingenious solution of yours.

Thank you