Server-side serial number validation with MSSQL and PHP

YouTube · Mon Apr 05, 2021 8:38 pm

Hello guys,

In our "How do I configure server-side serial number validation?" article, we offer a sample PHP script that connects to a MySQL database and checks whether the serial number input by the user at install time is valid or not.

Recently, one of our users requested a sample project / script that connects to a MSSQL database and checks whether the serial number is valid or not.

With that in mind, I have decided to write this article, hopefully other users facing a similar scenario will find this useful.

Normally, using this feature would require the user to be familiar with the programming language (in our case PHP) and Microsoft SQL (MSSQL).

However, I will try my best to explain all the steps required to achieve this, hopefully someone that is just getting started will also benefit from this article.

Prerequisites:

Advanced Installer Enterprise or above (since the "Licensing" feature is available starting with our "Enterprise" suite)

WampServer as the server that hosts the PHP file

: InstalledWamp.png (11.81KiB)Viewed 381869 times

SQL Server + Microsoft SQL Server Management Studio

Microsoft PHP Drivers for SQL Server

The Microsoft Drivers for PHP for SQL Server enable integration with SQL Server for PHP applications. The drivers are PHP extensions that allow the reading and writing of SQL Server data from within PHP scripts.

These would be the prerequisites required in order to achieve what we want, so let's get started!

First of all, here is the location on my machine that holds the PHP files:

Code: Select all

C:\wamp64\www\test

First thing I'd like to do here is checking whether we have the "Microsoft PHP Drivers for SQL Server" installed on our machine. To do so, in the above mentioned folder we need to create a file that will provide us some information about the PHP configuration. For instance, we can create a file called "info.php" that will have the following content:

Code: Select all

<?php
phpinfo();
?>

phpinfo is a function that outputs information about the PHP configuration.

We can then access the file by opening a browser and using a URL as it follows:

Code: Select all

localhost/test/info.php

The output is quite large, but it should look something as it follows:

: phpinfo.png (210.23KiB)Viewed 381869 times

Using the "Search" functionality (CTRL+S), we should look for the "Microsoft SQL Server Driver for PHP", namely:

sqlsrv

and:

pdo_sqlsrv

If we can not find it, we should proceed in downloading and installing it.

After downloading the "installer", it will ask for a location where it will extract multiple DLL files. Based on our PHP configuration, we will need two of the DLL files.

From the configuration file, we will need the following information:

Architecture - in our case, it is x64

PHP Extension Build - in our case, it is API20180731,TS,VC15

Loaded Configuration File - in our case, it is C:\wamp64\bin\apache\apache2.4.41\bin\php.ini

From the list of the DLL files, we will need to select the ones that will match our needs:

: TS_x64.png (93.09KiB)Viewed 381869 times

As we can see, we have selected the ones matching our configuration (namely x64 and TS).

We will now need to copy these DLL files to the extension folder (ext) for PHP. On my machine, the path looks as it follows:

Code: Select all

C:\wamp64\bin\php\php7.3.12\ext

Now, if we want to have the extension to be loaded automatically, we will need to modify the configuration file by adding our extensions to it.

As I have mentioned above, the "php.ini" file can be found in the following location:

Code: Select all

C:\wamp64\bin\apache\apache2.4.41\bin

In the configuration file, under the "Dynamic Extensions" we should add our extensions as it follows (this is also explained in the configuration file itself):

Code: Select all

extension=modulename

e.g.:

Code: Select all

extension=php_pdo_sqlsrv_73_ts_x64.dll
extension=php_sqlsrv_73_ts_x64.dll

It may look something as it follows:

: extension.png (87.57KiB)Viewed 381869 times

Save the file and reload the localhost/test/info.php page. You should now get the desired results when searching for either "sqlsrv" or "pdo_sqlsrv".

Now that we have got this out of our way, let us focus on:

the database

the script that we will host on our server. This script will interogate the database

Act 1 (just kidding, we are not actors) - The Database

Let's open Microsoft SQL Server Management Studio and create a new database, called "Users":

- right click on "Databases" --> "New Database"

- right click on the earlier created database --> "New Query"

Through this query, we will need to create the table and insert data into it. This may look something as it follows:

Code: Select all

-- Create the table

CREATE TABLE Clients(
client_id int,
user_name varchar(255),
company varchar(255),
email varchar(255),
serial_no varchar(255),
additional_information text
)

-- Add data to it

INSERT INTO Clients(client_id, user_name, company, email, serial_no,additional_information)
VALUES ('1', 'John Doe', 'User Company', 'johnd@domain.com', '233-421-752-325', 'additional information')
INSERT INTO Cleints(client_id, user_name, company, email, serial_no, additional_information)
VALUES('2', 'Catalin Gheorghe', 'Caphyon', 'catalingheorghe@domain.com', '123-456-789-000', 'The license assigned to Catalin')

-- In order to retrieve the data earlier added, we can query the table by using:
-- SELECT * FROM Clients

This should look something as it follows (after querying the database):

: Database.png (18.09KiB)Viewed 381869 times

Act 2 - The script that we will hold on our server and that will query the database

Let's create a new file in the /localhost/test/ folder and name it "license.php". Here is how the license file can look like:

Code: Select all

<?php

// server response codes
define('LICENSE_VALID',   '601');
define('LICENSE_INVALID', '602');


// MSSQL databse connection parameters
$db_host = 'DESKTOP-87HDD4V\SQLEXPRESS';
$db_user = 'sa';
$db_password = '12345';
$db_name = 'Users';

// client information table
$clients_tbl_name = 'Clients';
$sn_tbl_col       = 'serial_no';


function ServerResponse($is_valid, $posted_serial = '', $lang_id = 1033)
{
  $msg_sep = "\n";
  
  // load error messages from your database, using "$lang_id" for localization (optional)
  
  if($posted_serial == '')
    return LICENSE_INVALID . $msg_sep . "Missing Serial Number !";
  
  if($is_valid == true)
    return LICENSE_VALID;
  else
    return LICENSE_INVALID . $msg_sep . "Serial Number: " . $posted_serial . ' is invalid !';  
}

// Variables POSTed by Advanced Installer serial validation tool to this web page: "sn", "languageid".
// check if the POSTed variable is declared and different than NULL && check if the stripped variable is different than empty string
if(isset($_POST['sn']) && trim($_POST['sn']) != '')
{
  // get the serial number entered by the installing user in the "UserRegistrationDlg" dialog 
  $sn = trim($_POST['sn']);
  
  // get the system language ID of the user's machine
  // (you can use this parameter to display a localized error message taken from your database)
  $languageid = (int) $_POST['languageid'];

  // get the additional information entered by the installing user in the "UserRegistrationDlg" dialog 
  $additional_information = $_POST['ai'];



  // connect to MSSQL Server / Database

$connectionInfo = array("Database"=>"Users"); // connect using Windows Authentication
$conn = new PDO( "sqlsrv:server=$db_host;Database = $db_name");
$conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );  
$conn->setAttribute( PDO::SQLSRV_ATTR_QUERY_TIMEOUT, 1 );

// here I have checked whether the connection is succesful or not outside of Advanced Installer. The echo is commented because the server should 
// only return the response code, e.g.: 601 or 602 without any additional information. If the echo is not commented, the server would return
// something like "Connection established.601" and this would be an invalid response for the Custom Action that Advanced Installer uses
if( $conn ) {
     //echo "Connection established.";
}else{
     echo "Connection could not be established.";
     die( print_r( sqlsrv_errors(), true));
}

  // prepare the SQL query
  $query = "SELECT serial_no FROM Clients WHERE serial_no ='$sn'";

  // more information about the CURSOR types here:
  // https://docs.microsoft.com/en-us/sql/connect/php/cursor-types-pdo-sqlsrv-driver?view=sql-server-ver15
  $stmt = $conn->prepare( $query, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL, PDO::SQLSRV_ATTR_CURSOR_SCROLL_TYPE => PDO::SQLSRV_CURSOR_BUFFERED) );

  // execute the statemed (SQL Query)
  $stmt->execute();

  // this was also used to verify the behavior outside of Advanced Installer. This was used with a query such as SELECT * FROM Clients
  // to see whether the connection is established and the script queries the database
while ( $row = $stmt->fetch( PDO::FETCH_ASSOC ) ){  
    //print_r( $row['serial_no'] ."\n" );  
}

  // get the result set
  $row_count = $stmt->rowCount();
    //echo $row_count;
  
  if($row_count == 0)
  {
    // serial number NOT found in database => issue error response
    echo ServerResponse(false, $sn, $languageid);
    die();
  }
  else
  {
    // serial number was found in database => issue SUCCESS response
    echo ServerResponse(true, $sn, $languageid);
    die();
  }
}  
  else
{
  // issue error response
  echo ServerResponse(false);
  die();
}

?>

Let's try and test this outside of Advanced Installer first and then implement & test it in Advanced Installer.

So basically we know that our predefined custom action will POST the information entered by the user in the "UserRegistrationDlg" dialog to our server and then wait for a response.

If we take a look over the PHP script, we will notice that there are three variables that the function expects and that therefore should be POSTed, namely: sn, languageid and ai (additional information).

We can create a POST request by using PowerShell and its' Invoke-WebRequest cmdlet.

The script may look something as it follows:

Code: Select all

# create a hashtable with the parameters POSTed to the server and their values
$postParams = @{sn='123-456-789-000'; languageid='1033'; ai='additional information'}

# POST the variables to our server
Invoke-WebRequest -Uri http://localhost/test/license.php -Method POST -Body $postParams

: PowerShell.png (82.95KiB)Viewed 381869 times

As we can see, the server returns "601", meaning that it found the serial number in the database. Hooray!

Let's also test this and see how it responds when the serial number can not be found in the database:

: PowerShell 2.png (84.3KiB)Viewed 381869 times

As we can see, if the serial number is not found in the database, the server will return "602".

Here are the steps to implement this in Advanced Installer:

1. open Advanced Installer

2. create an Enterprise project

3. "Licensing" page --> "User serial validation"

4. select the "Server-side validation" option

5. in the URL field, input the location to the PHP script, e.g.:

Code: Select all

http://localhost/test/license.php

6. modify the template of the license, e.g.:

Code: Select all

<###-###-###-###>

since we expect a license format as it follows:

Code: Select all

123-456-789-012

Please also find attached, for your reference, a sample project that implements the above steps:

Server-side Serial Number Validation - MSSQL Sample.aip: (14.07KiB)Downloaded 2626 times

Important note:

Please note that I am by no means a PHP or SQL expert. As its' name suggests, this is just a "sample" whose purpose is to get you started with our "Licensing" feature.

Hope you will find this useful! ${L_SMILIES_SMILE}$

Best regards,
Catalin

Suggested Articles

User Guide

Videos

Server-side serial number validation with MSSQL and PHP

Server-side serial number validation with MSSQL and PHP

Caphyon Products

Company

Product

Resources

Free Tools

Connect