Windows 10 and PHP,MS SQL Server + Apache

PHP And SQL for Windows 10 x64

My Platform for setup is

• PHP 5.6.17 64 bit
• SQL Server 2016 64 bit
• Apache 2.4.17 64 bit
• The Editor - Sublime 2

I am using Wamp Server 3.0 64 bit mainly because my copy of SQL is 64 bit and i want some conformity as the drivers need to match SQL server to PHP  

Tricks to get PHP to talk to SQL


Get Drivers from http://robsphp.blogspot.com.au/2012/06/unofficial-microsoft-sql-server-driver.html

• If you need 64 bit use drivers from x64 folder
• If your PHP server is a Thread Safe version use the thread safe dll (if it doesn't load try the non threadsafe dll )
• Make sure  the drivers are copied into the ext directory listed in my.ini
• Add the Drivers to the Extension part of the ini file , ie Extension = driver name
• Restart PHP to load the drivers

if your unsure if your php server is thread safe or not run the code below in getconfig.php

<?php

  phpinfo();

?>

save this to getconfig.php and open your browser and browse to ( in my case localhost) localhost\getconfig.php

you should see as below if its thread safe or not.

Also if you have loaded the Sql Drivers correctly ( php_pdo_sqlsrv & php_sqlsvr) - version and thread type not specified you should see this if its loaded correctly.

Once you have confirmed that the drivers are installed , make sure your SQL server has SQL Server and Windows Authentication Enabled , not just Windows Authentication Enabled. Once that is confirmed ( if you have to change it , the sql server need to be re started.) Add a user if you don't have one that's got Sql Server Authentication instead and give it the access it needs ( user rights , access to the db your going to use).

On Windows 10 you can use IIS but i found on my copy of window 10 64 bit it was actually 32 bit and it didn't play ball with the drivers for PHP as IIS installed a 32 bit copy of PHP and the drivers needed to be 64 bit as my SQL server is 64 bit. Instead i used Wamp Server 3.0 and ignored the fact MySql Does'nt work due to access denied issues on Windows 10.

Once you have confirmed you have the drivers working the below script can test to see if PHP can talk to SQL.

Save the above script file as testmssql.php and run it in your browser to ensure your php can connect to your sql server.

In my next blog i'll talk about encryption on the sql server

Data Encryption in Sql Server

JAN

31

Protecting User Data

For the last 12 to 18 months I've been on a mission to ensure any user data i stored in a sql data base is well encrypted not just encrypted but with some thought behind so if someone steals my data on the sql server they will just nothing of any value.

For this i am using rc4 which i found on sqlteam when i googled SQL RC4 , having said that the version i found had a few issues and i've since taken it apart and fixed it so it works to encrypt and decrypt strings with out issue.

For my implementation of this RC4 encryption i have a master key set to 100 to 120 bytes, which is used to encrypt the users field they want to protect , ie username or email address, mind you i didn't stop there, my thoughts were what if the username was very small , ie 4 or characters , my answer to that would be that for instance the username field in my database is 100 characters wide (varchar(100)) i would produce an encrypted string that is 100 characters wide.

Now your wondering how do i go from lets say 5 characters of encrypted data to 100 ? . simple i pad the now encrypted with a character of the callers choice ( lets say 10 possible characters to choose from) and then encrypt this 100 character string with a new key , not just any key but a key derived from the master encryption key. 

The reason i use a derived key is this is not stored anywhere but its produced on the sql server in a stored function that is a wrapper around the RC4 encryption routine , this derived key is formed by doing a process of grabbing every x number of characters IE every 7 counting up from 0 till the end of the master key , then grabbing every y characters ie every 5 counting down from the length of the master key until we can grab no more characters , and repeat these two steps until we have a derived key that matches the length of the master encryption key.

Now their's one final step , its done at the just before the data is encrypted  , the caller specify's the starting position for the master encryption key. How I've implemented it lets say your key is 120 characters long and you specify that the start will be at 45 characters , i build the encryption key using a sql command like this ( @Start is set to 45 for this example)

The function that does this , has the data to encrypt passed in , the length of the encrypted field , the start position and the desired index of the padding key ( IE to a linked table with x number of padding characters in it)

To See this in action here is what the data might look like ( I've cut the field down to fit the image in)

In My next blog i'll talk about how this is done in a SQL Query in PHP