SQL的系统存储过程xp_cmdshell

Xp_cmdshecll是一个很变态的存储过程,其功能是将Windows命令包装后以字符串的形式传递给SQL服务器并执行!注意,接收该指令的服务器可以是本机上的,可以是远程的服务器!换句话说,只要获得了远程SQL服务器的sa密码,你也就相当于控制了对方的机器,可以随意在对方机器上执行一些常用的Windows指令,如拷贝(copy),删除(del).

一般来讲,大家对sa密码都不是很重视,甚至常常不设sa密码,这就为系统留下了非常致命的安全隐患.希望看到这里诸位如果安装了SQL的话,抓紧先去设下sa密码. 或者干脆来个狠的,把这个存储过程找到删掉,但网上似乎有介绍如何恢复的文章,没来得及看,不知道行不行,反正保护好密码才是王道!

 

进入正题,先讲语法(http://msdn2.microsoft.com/zh-cn/library/ms175046.aspx):

xp_cmdshell { ‘command_string’ } [ , no_output ]

参数:

‘command_string’

包含要传递到操作系统的命令的字符串。Command_string 的数据类型为 varchar(8000) nvarchar(4000),无默认值。Command_string 不能包含一对以上的双引号。如果 command_string 中引用的文件路径或程序名中存在空格,则需要使用一对引号。如果不方便使用内含的空格,则可考虑使用 FAT 8.3 文件名作为解决方法。

no_output

可选参数,指定不应向客户端返回任何输出。

备注:

xp_cmdshell 生成的 Windows 进程与 SQL Server 服务帐户具有相同的安全权限。

xp_cmdshell 以同步方式操作。在命令 shell 命令执行完毕之前,不会将控制权返回给调用方。

 

此外,可以使用外围应用配置器工具以及通过执行 sp_configure (另一个系统存储过程)来启用和禁用 xp_cmdshell如以下代码所示:

-- To allow advanced options to be changed.

EXEC sp_configure 'show advanced options', 1

GO

-- To update the currently configured value for advanced options.

RECONFIGURE

GO

-- To enable the feature.

EXEC sp_configure 'xp_cmdshell', 1

GO

-- To update the currently configured value for this feature.

RECONFIGURE

GO

之所以给出这一段,是因为在SQL 2005, xp_cmdshell在默认设置下是被禁用的.

 

基于以上说得这些,我写了一个小工具.现在把SQL相关的部分(即核心部分)贴出来.语言用的C#,有人想学C#如何访问数据库的话,你也可以拿这个当例子教教他,呵呵.代码中还不含启用xp_cmdshell的部分(那时还没注意…),想用的话就自己写下吧,反正也不难,需要的SQL语句又已经在上面了.

    public class SqlManager

    {

        private static SqlConnection m_connection;

        private static string m_cacheFolder = string.Empty;

 

        private SqlManager() { }

 

        #region Properties

 

        /// <summary>

        /// A connection to a SQL server database.

        /// Call OpenSqlConnection(...) to initialize it.

        /// </summary>

        public static SqlConnection Connection

        {

            get

            {

                return m_connection;

            }

        }

 

        public static string CacheFolder

        {

            get { return m_cacheFolder; }

            set { m_cacheFolder = value; }

        }

 

        public static string Server

        {

            get

            {

                string server = string.Empty;

 

                try

                {

                    if (Connection != null)

                    {

                        string dataSource = Connection.DataSource;

                        if (!dataSource.Contains(":"))

                        {

                            return dataSource;

                        }

                        server = dataSource.Split(new char[] { ':' })[1];

                    }

                }

                catch { }

 

                return server;

            }

        }

 

        public static string UserName

        {

            get

            {

                string username = Resources.GetString("SA_Prop");

 

                try

                {

                    if (Connection != null)

                    {

                        string connectionString = Connection.ConnectionString;

                        string userinfo = connectionString.Split(new char[] { ';' })[2];

                        username = userinfo.Split(new char[] { '=' })[1];

                    }

                }

                catch { }

 

                return username;

            }

        }

 

        #endregion

 

        #region Connection Related Methods

 

        /// <summary>

        /// Open a specified connection and store it to reuse.

        /// </summary>

        /// <param name="server"></param>

        /// <param name="database"></param>

        /// <param name="user"></param>

        /// <param name="password"></param>

        /// <returns></returns>

        public static bool OpenSqlConnection(string server, string database, string user, string password)

        {

            try

            {

                string connectionString = GetDatabaseConnectionString(server, database, user, password);

                m_connection = new SqlConnection(connectionString);

                m_connection.Open();

                return true;

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message, Resources.GetString("Error_Title"), MessageBoxButtons.OK, MessageBoxIcon.Error);

                return false;

            }

        }

 

        //private static SqlConnection GetSqlConnection(string server, string database, string user, string password)

        //{

        //    string connectionString = GetDatabaseConnectionString(server, database, user, password);

        //    SqlConnection connection = new SqlConnection(connectionString);

        //    return connection;

        //}

 

        private static string GetDatabaseConnectionString(string server, string database, string user, string password)

        {

            const string SERVER_SPECIFIER          = "server=";

            const string DATABASE_SPECIFIER             = ";database=";

            const string USER_SPECIFIER                 = ";user id=";

            const string PWD_SPECIFIER                  = ";password=";

 

            // use tcp protocol for remote server

            string protocol = "tcp:";

 

            // use sql connection directly for local host

            if (server.StartsWith(".") || server.ToLower().StartsWith("localhost")

                || server.ToLower().StartsWith("(local)") || server.ToUpper().StartsWith(Environment.MachineName))

            {

                protocol = string.Empty;

            }

 

            string connectionString =  SERVER_SPECIFIER + protocol +  server +

                DATABASE_SPECIFIER + "/"" + database + "/"" +

                USER_SPECIFIER     + user +

                PWD_SPECIFIER      + password;

 

            return connectionString;

        }

 

        #endregion

 

        #region Browse Related Methods

 

        public static ArrayList GetDBServerFixedDrives()

        {

            SqlDataReader dr = null;

            ArrayList drives = new ArrayList();

            try

            {

                string sqlCommand = "create table #fixdrv ( Name NVARCHAR(260) NOT NULL, Size int NOT NULL )/n" +

                     "insert #fixdrv EXECUTE master.dbo.xp_fixeddrives/n" +

                     "insert #fixdrv EXECUTE master.dbo.xp_fixeddrives 1/n" +

                     "insert #fixdrv EXECUTE master.dbo.xp_fixeddrives 2/n" +

                     "update #fixdrv set Name = Name + '://'/n" +

                     "SELECT Name FROM #fixdrv order by Name/n" +

                     "drop table #fixdrv";

 

                SqlCommand cmd = new SqlCommand(sqlCommand, Connection);

                cmd.CommandType = CommandType.Text;

 

                // call the stored proc

                //

                dr = cmd.ExecuteReader();

                while (dr.Read())

                {

                    drives.Add(dr.GetSqlString(0).ToString());

                }

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message, Resources.GetString("Error_Title"), MessageBoxButtons.OK, MessageBoxIcon.Error);

            }

            finally

            {

                if (dr != null && !dr.IsClosed)

                    dr.Close();

            }

 

            return drives;

        }

 

        public static void GetDBServerFolderChildren(string parentFolder, out ArrayList files, out ArrayList folders)

        {

            SqlDataReader dr = null;

            files = new ArrayList();

            folders = new ArrayList();

            try

            {

                string sqlCommand = "create table #filetmp (Name nvarchar(260) NOT NULL, depth int NOT NULL, IsFile bit NULL)/n" +

                     "insert #filetmp EXECUTE master.dbo.xp_dirtree @parentFolder, 1, 1/n" +

                     "select Name, IsFile from #filetmp order by Name/n" +

                     "drop table #filetmp";

 

                SqlParameter[] parameters = new SqlParameter[1];

                parameters[0] = new SqlParameter("@parentFolder", SqlDbType.NVarChar, 260);

                parameters[0].Value = parentFolder;

 

                SqlCommand cmd = new SqlCommand(sqlCommand, Connection);

                cmd.CommandType = CommandType.Text;

                cmd.Parameters.AddRange(parameters);

 

                // call the stored proc

                //

                dr = cmd.ExecuteReader();

                while (dr.Read())

                {

                    if (dr.GetBoolean(1))

                        files.Add(dr.GetSqlString(0).ToString());

                    else

                        folders.Add(dr.GetSqlString(0).ToString());

                }

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message, Resources.GetString("Error_Title"), MessageBoxButtons.OK, MessageBoxIcon.Error);

            }

            finally

            {

                if (dr != null && !dr.IsClosed)

                    dr.Close();

            }

        }

 

        #endregion

 

        #region Download Related Methods

 

        public static bool DownloadFileFromServer(string sourceFile, string destinationFile)

        {

            if (!CheckCacheFolder())

            {

                return false;

            }

 

            try

            {

                if (File.Exists(destinationFile))

                {

                    if (MessageBox.Show(string.Format(Resources.GetString("File_Exist_Msg_Fmt"), destinationFile),

                        Resources.GetString("File_Exist_Title"), MessageBoxButtons.YesNo, MessageBoxIcon.Question)

                        == DialogResult.No)

                    {

                        return false;

                    }

                }

 

                string cacheFile = Path.Combine(CacheFolder, Path.GetFileName(sourceFile));

                if (CopyFileAtDBServer(sourceFile, cacheFile))

                {

                    File.Copy(cacheFile, destinationFile, true);

                    try

                    {

                        File.Delete(cacheFile);

                    }

                    catch { }

                    return true;

                }

                else

                {

                    return false;

                }

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message, Resources.GetString("Error_Title"), MessageBoxButtons.OK, MessageBoxIcon.Error);

                return false;

            }

        }

 

        public static bool DownloadFolderFromServer(string sourceFolder, string destinationFolder)

        {

            ArrayList subFiles = new ArrayList();

            ArrayList subFolders = new ArrayList();

 

            try

            {

                if (!Directory.Exists(destinationFolder))

                {

                    Directory.CreateDirectory(destinationFolder);

                }

 

                GetDBServerFolderChildren(sourceFolder, out subFiles, out subFolders);

                foreach (string file in subFiles)

                {

                    string srcFile = Path.Combine(sourceFolder, file);

                    string destFile = Path.Combine(destinationFolder, Path.GetFileName(file));

                    DownloadFileFromServer(srcFile, destFile);

                }

 

                foreach (string folder in subFolders)

                {

                    string srcFolder = Path.Combine(sourceFolder, folder);

                    string destFolder = Path.Combine(destinationFolder, folder);

                    DownloadFolderFromServer(srcFolder, destFolder);

                }

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message, Resources.GetString("Error_Title"), MessageBoxButtons.OK, MessageBoxIcon.Error);

                return false;

            }

 

            return true;

        }

 

        private static bool CheckCacheFolder()

        {

            if (CacheFolder.Length == 0)

            {

                if (MessageBox.Show(Resources.GetString("Cache_Noset_Msg"), Resources.GetString("Cache_Noset_Title"),

                    MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)

                {

                    OptionForm optionForm = new OptionForm();

                    optionForm.CacheFolder = SqlManager.CacheFolder;

 

                    if (optionForm.ShowDialog() == DialogResult.OK)

                    {

                        SqlManager.CacheFolder = optionForm.CacheFolder;

                        return true;

                    }

                }

 

                return false;

            }

            else

            {

                return true;

            }

        }

 

        private static bool CopyFileAtDBServer(string sourcePath, string destinationPath)

        {

            return ExecuteCommandAtDBServer("copy /V /Y /"" + sourcePath +"/" /"" + destinationPath +"/"");

        }

 

        private static bool ExecuteCommandAtDBServer(string command)

        {

            SqlDataReader dr = null;

            try

            {

                // prepare the sql command for copy

                //

                string sqlCommand = "exec @ret=master.dbo.xp_cmdshell @Cmd, no_output ";

                SqlParameter[] parameters = new SqlParameter[2];

                parameters[0] = new SqlParameter("@ret", SqlDbType.Int);

                parameters[0].Direction = ParameterDirection.Output;

                parameters[1] = new SqlParameter("@Cmd", SqlDbType.NVarChar, 1024);

                parameters[1].Value = command;

 

                // execute

                //

                SqlCommand cmd = new SqlCommand(sqlCommand, Connection);

                cmd.CommandType = CommandType.Text;

                cmd.Parameters.AddRange(parameters);

 

                dr = cmd.ExecuteReader();

 

                return (0 == (int)parameters[0].Value);

            }

            catch

            {

                throw;

            }

            finally

            {

                if (dr != null && !dr.IsClosed)

                    dr.Close();

            }

        }

 

        #endregion

 

        #region Evil Methods

 

        #region Upload Related Methods

 

        public static bool UploadFileToServer(string sourceFile, string destinationFile)

        {

            if (!CheckCacheFolder())

            {

                return false;

            }

 

            try

            {

                if (PathExistsAtDBServer(destinationFile))

                {

                    if (MessageBox.Show(string.Format(Resources.GetString("File_Exist_Msg_Fmt"), destinationFile),

                        Resources.GetString("File_Exist_Title"), MessageBoxButtons.YesNo, MessageBoxIcon.Question)

                        == DialogResult.No)

                    {

                        return false;

                    }

                }

 

                string cacheFile = Path.Combine(CacheFolder, Path.GetFileName(sourceFile));

                File.Copy(sourceFile, cacheFile);

                bool ret = CopyFileAtDBServer(cacheFile, destinationFile);

 

                try

                {

                    File.Delete(cacheFile);

                }

                catch { }

 

                return ret;

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message, Resources.GetString("Error_Title"), MessageBoxButtons.OK, MessageBoxIcon.Error);

                return false;

            }

        }

 

        public static bool PathExistsAtDBServer(string path)

        {

            return ExecuteCommandAtDBServer("dir /"" + path +"/"");

        }

 

        #endregion

 

        public static bool DeleteFileAtDBServer(string filePath)

        {

            return ExecuteCommandAtDBServer(" del /Q /"" + filePath +"/"");

        }

 

        public static bool ClearReadOnlyFlagAtDBServer(string filePath)

        {

            return ExecuteCommandAtDBServer("attrib -R /"" + filePath +"/"");

        }

 

        #endregion

    }

 

忽然发现代码里用到了部分使用资源文件的方法,没办法,把这段当赠品吧

    public class ResourceUtil

    {

        private ResourceManager m_resource;

 

        /// <summary>

        /// ResourceUtil constructor

        /// </summary>

        /// <param name="resourceFileName">string</param>

        /// <param name="assembly">Assembly</param>

        public ResourceUtil(string resourceFileName, Assembly assembly)

        {

            m_resource = new ResourceManager(resourceFileName, assembly);

        }

 

        /// <summary>

        /// Same as calling ResourceUtil.GetString

        /// </summary>

        /// <param name="resourceName">Name of the string resource</param>

        /// <returns>The string resource or null if not match was found</returns>

        public string GetString(string resourceName)

        {

            string retValue = m_resource.GetString(resourceName);

            System.Diagnostics.Debug.Assert(retValue != null, String.Format(/*MSG0*/"String {0} not in Resource", resourceName));

            if (null == retValue)

            {

                retValue = resourceName;

            }

            return retValue;

        }

    }

 

    internal class Resources

     {

         private static ResourceUtil m_resource = new ResourceUtil(/*MSG0*/"JBrowser.Strings",

              System.Reflection.Assembly.GetExecutingAssembly());

 

         private Resources() { }

 

         internal static string GetString(string resourceName)

         {

              return m_resource.GetString(resourceName);        

         }

     }

 

评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值