Managing your active SQL Server instance list using PHP

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

REM Go to the folder that holds the PHP executables
cd c:\YourFolder
cd PHP7
cls
php -S localhost:8000

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;
}		

// 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);

// 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 );

<form action ="Example_Recover_Instance.php" method="POST">

// 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']);
}

function connect_to_survey(){ // Purpose: return the connection resource to the survey database.

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;
}

# 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"