Monday, September 07, 2009

Adding direct SQL editing/browsing access to any website...

I was editing a nopcommerce website that I only had http and ftp access to - no sql access.

So I needed a way to execute some SQL scripts.

To do this I added (to the administration pages) the following

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="SQLRunner.aspx.cs" MasterPageFile="~/Administration/main.master" Inherits="NopSolutions.NopCommerce.Web.Administration.SQLRunner" %>

<asp:Content ID="Content1" ContentPlaceHolderID="cph1" runat="server">
    SQL:
    <br />
    <asp:TextBox ID="tbSQL" runat="server" Columns="80" Rows="10" TextMode="MultiLine">
    </asp:TextBox>
    <br />
    <asp:CheckBox ID="cbScript" runat="server" Text="Run as script" />
    <asp:Button ID="btnGo" runat="server" Text="Go" OnClick="btnGo_OnClick" />
    <br />
    <asp:Panel ID="pnlOutput" runat="server" Visible="false">
        <asp:GridView ID="grdResults" runat="server"></asp:GridView>
    </asp:Panel>
    <asp:Label ID="lblResult" runat="server" >
    </asp:Label>
</asp:Content>


coupled with this source

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using NopSolutions.NopCommerce.Web;
using NopSolutions.NopCommerce.DataAccess;
using System.Configuration;
using System.Data.SqlClient;

namespace NopSolutions.NopCommerce.Web.Administration
{
    public partial class SQLRunner : BaseNopAdministrationPage
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void btnGo_OnClick(object sender, EventArgs e)
        {
            try
            {
                var sqlConnection = NopSqlDataHelper.CreateConnection(ConfigurationManager.ConnectionStrings["NopSqlConnection"].ConnectionString);
                var sqlCommand = sqlConnection.GetSqlStringCommand(tbSQL.Text);
                if (cbScript.Checked)
                {
                    sqlConnection.ExecuteNonQuery(sqlCommand);

                    pnlOutput.Visible = false;
                }
                else
                {
                    var dataSet = sqlConnection.ExecuteDataSet(sqlCommand);

                    pnlOutput.Visible = true;
                    grdResults.DataSource = dataSet;
                    grdResults.DataBind();
                }
                lblResult.Text = "OK";
            }
            catch (Exception exc)
            {
                lblResult.Text = string.Format("Exception seen - {0} - {1}", exc.GetType().Name, exc.Message);
            }
        }
    }
}

Seems to work OK :)

No comments:

Post a Comment