By: Graham Okely | Updated: 2018-08-14 | Comments | Related: More > Database Administration
Problem
What SQL Server instances do you manage? What environment is each one in? Can you add or remove instances from your master list easily? In this tip, we will look at a simple way to manage your list of SQL Server instances using PHP and SQL Server.
Solution
In this tip some PHP code is discussed to help you manage a list of SQL Server instances in a SQL Server database table. We will show how to sanitize your input and user parameters to insert data safely into your database.
Scope
This is not designed to be an internet facing solution. Keep this on your internal network and we recommend you turn off the web site when it is not in use.
Design
I chose a simple, one page, design that has three sections. First, add an instance, second display the list with the ability to archive an instance and third recover an archived instance. They are the three main tasks when managing a list of instance names.
Database Design - Tables
Three tables will do for this project. After a few design changes I found a separate Environment table helped.
USE [Admin] GO CREATE SCHEMA [Instances] GO CREATE TABLE [Instances].[Active]( [Instance] [nvarchar](2048) NULL ) CREATE CLUSTERED INDEX [ClusteredIndex_Instance] ON [Instances].[Active]([Instance] ASC) GO CREATE TABLE [Instances].[Environment]( [Instance] [nvarchar](2048) NULL, [Environment] [nvarchar](2048) NULL, [Comments] [nvarchar](max) NULL ) CREATE CLUSTERED INDEX [ClusteredIndex_Instance] ON [Instances].[Environment]([Instance] ASC) GO CREATE TABLE [Instances].[Archived]( [Date_Stamp] [datetime] NULL, [Instance] [nvarchar](2048) NULL, [Comments] [nvarchar](max) NULL ) CREATE CLUSTERED INDEX [ClusteredIndex_Instance] ON [Instances].[Archived]([Instance] ASC) GO
Why PHP?
When studying for my degree in Information Technology I studied C, C#, C++, PERL, Java and PHP. I concluded that when it came to simple administrative purposes PHP was easy to use. C and C++ was too low level, PERL too cryptic, Java while easy to use was not built with Web pages in mind, so that left PHP.
Understand the activity of each layer
The PHP generates the HTML. The HTML builds FORMS that have ACTIONS associated to them. The ACTIONS cause T-SQL to be applied to the database engine which pushes or pulls data from the underlying database.
Step 1 - Install PHP
Place PHP on your own laptop or desktop. That way you know when it is shut down. Download PHP7 to any folder.
Step 2 - Install the PHP Drivers
Microsoft guide on SQL Server PHP drivers.
Step 3 - PHP7 Has a built-in simple IIS web server
As PHP has a simple IIS web server. Keep it simple and use that. These commands get it going:
REM Go to the folder that holds the PHP executables cd c:\YourFolder cd PHP7 cls php -S localhost:8000
Step 4 - Security
This project it not designed to go on the internet. It is designed for DBA use alone. Even in that case we will still sanitize all input. To do that we will use this simple method to escape strings in SQL Server from stackoverflow.
Here is an explanation of the sanitize function from StackOverFlow.
function sanitize_all_input( $RawData ){ # Idea taken from # https://stackoverflow.com/questions/574805/how-to-escape-strings-in-sql-server-using-php // Check there is data to process // Note this test is a SIMPLE test only. Suitable for inhouse systems. if ( !isset($RawData) or empty($RawData) ) return ''; // Purely numeric data is not a concern. if ( is_numeric($RawData) ) return $RawData; // Remove these control characters that will break things. // They do not display in browsers. $non_displayables = array( '/%0[0-8bcef]/', // url encoded 00-08, 11, 12, 14, 15 '/%1[0-9a-f]/', // url encoded 16-31 '/[\x00-\x08]/', // 00-08 '/\x0b/', // 11 '/\x0c/', // 12 '/[\x0e-\x1f]/' // 14-31 ); // Loop through the expressions and perform a regular expression search and replace. // http://php.net/manual/en/function.preg-replace.php foreach ( $non_displayables as $regex ) $RawData = preg_replace( $regex , '' , $RawData ); // Don't think too long about how that line works! // Finally add a single quote to any existing single quotes in the input. $CleanData = str_replace("'", "''", $RawData ); return $CleanData; }
Parameters
It is possible to use parameters when inserting data into the database.
// Track the instance and its environment $SQL_String_Instance = "insert into [Admin].[Survey].[Environment] select ? , ? , ? "; $Parameters_All = array($Instance_To_Process, $Environment, $Comments); $stmt = sqlsrv_query( $Connection, $SQL_String_Instance, $Parameters_All);
However, I find that makes things less easy to read. The code below is easier to read and debug, but does mean you need to sanitize all input and that is a good idea all the time.
// Track the instance and its environment $SQL_String_Instance = "insert into [Admin].[Survey].[Environment] select '$Instance_To_Process', '$Environment', '$Comments' "; $stmt = sqlsrv_query( $Connection, $SQL_String_Instance );
But where should you sanitize the input? It is a good idea to do that as soon as you can. So that means in the script processing for the form.
<form action ="Example_Recover_Instance.php" method="POST">
In this case inside the file titled: "Example_Recover_Instance.php". Here is the PHP script inside that file to do that.
// Initialize the variable $Instance_To_Recover = ''; // Check if we have data if ( ! empty( $_POST['Instance_To_Recover'] ) ){ // Sanitize and use that data $Instance_To_Recover = sanitize_all_input($_POST['Instance_To_Recover']); }
Step 5 - Functions
I place all functions into a file titled: Example_Common_Functions.php.
It is possible to build object oriented classes in PHP, however that is out of scope for this tip. When I title functions, I place a purpose as a comment as well. That allows me to scan the file and display the lines beginning with ‘function’ and get the function name and purpose displayed.
function connect_to_survey(){ // Purpose: return the connection resource to the survey database.
Step 6 - Format T-SQL
This is a simple formatter for T-SQL. I like to see the T-SQL running in the background. It allows me to copy the T-SQL out to SSMS and test it.
function format_tsql( $SQL_String ){ // Purpose: // Commence a new line on all these keywords $BreakOn=array( "SELECT","Select","select", "PRINT ","Print ","print ", "END","End", "BEGIN","Begin", "FROM ","From ","from ", "INSERT ","Insert ", "IF ","if ","IFF(","IFf(","iff(", "GROUP BY","Group by ","group by ", "LEFT JOIN ","Left Join ","left join ", "RIGHT","Right","right", "WHERE ","Where ","where ", "ORDER BY ","Order by ","order by ", "DECLARE ","Declare " ,"--" ); foreach ( $BreakOn as $term ) { $SQL_String=str_replace($term,strtoupper('</br><font color="blue">'.$term.'</font>'), $SQL_String); } // Build the final formatted SQL string. $Formatted='<code>'; // Sanitize TSQL just in case! $Formatted.=sanitize_all_input($SQL_String); $Formatted.='</code><hr>'; return $Formatted; }
Step 7 - List of files
This is a list of files and their purposes. Click on the link to download them. Try to read and understand them prior to using them.
File name | Purpose |
---|---|
Example_Add_Instance.php | Add an instance script. Without using parameters. |
Example_Add_Instance_Using_Parms.php | Add an instance script. Using parameters. (This is not used by the main form.) |
Example_Common_Functions.php | All the functions that help. |
Example_Delete_Instance.php | Remove an instance from the Archived table. |
Example_Manage_Instances.php | The main page with the active list. |
Example_Recover_Instance.php | Move the instance from the Archive table to the Active table. |
Example_Remove_Instance.php | Archive an instance name. |
Using the list of instances
This how you would typically use that list of instances that we manage?
Here is an example for PowerShell:
# File name : Loop Instances Example.ps1 # Reference : https://docs.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps # Clear the display Clear # Select some instances to process $SQL_String = "Select [Environment] , [ACTIVE].[Instance] FROM [Instances].[Active] [ACTIVE] left join [Instances].[Environment] [ENVIRONMENT] on [ACTIVE].Instance= ENVIRONMENT.Instance Where Environment='3 Development' Order by Environment, [ACTIVE].Instance" write-host "Running this query: $SQL_String" $InstanceList = Invoke-Sqlcmd -ServerInstance "DBA_ADMIN_2017" -Query $SQL_String -Database "Admin" $Count=0 foreach ( $Instance in $InstanceList ){ $Instance.Environment + ' ' + $Instance.Instance $Count=$Count+1 # Add your own commands here # See the Next Steps links below for ideas. } write-host "Processed $Count instances"
Conclusion
This tip is to help the DBA manage an instance list. Resist the temptation to publish your web pages over the internet or even the intranet.
Next Steps
- Check out tip 3549 by Jeffrey Yao for ideas on how to use your list of Instances.
- See tip 2660 by Diana Moldovan on iterating through SQL Servers.
- Check out how to locate your SQL Server instance in tip 2013 by Edwin Sarmiento.
- Check out why listing all your SQL Server does not work in tip 2057 by K. Brian Kelley.
- Check out more PHP commands available to you.
- Download the scripts for this tip.
About the author
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2018-08-14