Catalin
Posts: 6509
Joined: Wed Jun 13, 2018 7:49 am

Server-side serial number validation with MSSQL and PHP

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
InstalledWamp.png (11.81KiB)Viewed 382221 times
  • SQL Server + Microsoft SQL Server Management Studio
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
phpinfo.png (210.23KiB)Viewed 382221 times

Using the "Search" functionality (CTRL+S), we should look for the "Microsoft SQL Server Driver for PHP", namely:
and:
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
TS_x64.png (93.09KiB)Viewed 382221 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
extension.png (87.57KiB)Viewed 382221 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
Database.png (18.09KiB)Viewed 382221 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
PowerShell.png (82.95KiB)Viewed 382221 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
PowerShell 2.png (84.3KiB)Viewed 382221 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:
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! :)

Best regards,
Catalin
Catalin Gheorghe - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube

Return to “Sample Projects”