phpmssql
Contents
About
Current possibilities
Both possibilities utilize unixodbc. So they are probably very portable to other langunages than php.
Old methods
php-pdo-dblib (php-sybase)
The PHP pdo-dblib reference states
PDO_DBLIB is a driver that implements the PHP Data Objects (PDO) interface to enable access from PHP to Microsoft SQL Server and Sybase databases through the FreeTDS library.
This extension is not available anymore on Windows with PHP 5.3 or later.
On Windows, you should use SqlSrv, an alternative driver for MS SQL is available from Microsoft: http://msdn.microsoft.com/en-us/sqlserver/ff657782.aspx
If it is not possible to use SqlSrv, you can use the PDO_ODBC driver to connect to Microsoft SQL Server and Sybase databases, as the native Windows DB-LIB is ancient, thread un-safe and no longer supported by Microsoft.
php-db2
https://pear.php.net/package/DB
php-db2 is deprecated in favor of #php-mdb2.
php-mdb2
https://pear.php.net/package/MDB2
php-mdb2 does not provide a MSSQL driver on Debian.
sqlsrv
Microsoft repository
Installation
Add microsoft repository
Install client packages
1 ACCEPT_EULA=Y apt install msodbcsql17 mssql-tools
Create a profile to be sourced during login, which than in turn adds the new directory to the path. /etc/profile.d/mssql-tools.sh
Login again.
The 3rd party repository of Microsoft probably serves more current packages than in Debian. So make sure to upgrade.
1 apt upgrade
Basic test
Test it from cli
1 echo $PATH
2 /usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/opt/mssql-tools/bin
3 read PASSWORD
4 _PASSWORD_
5 # sqlcmd -S 192.168.19.3 -U username -P "$PASSWORD" -Q "SELECT @@VERSION"
6 Password:
7
8 ---------------------------------------------------------------------------------------------------------
9 Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64)
10 Apr 29 2016 23:23:58
11 Copyright (c) Microsoft Corporation
12 Standard Edition (64-bit) on Windows Server 2019 Datacenter 6.3 <X64> (Build 17763: ) (Hypervisor)
13
14
15 (1 rows affected)
Test the interactive shell
Configure PHP
Install package php-dev to get phpize
1 apt install php-dev dh-php
Install php driver via pecl
1 pecl install sqlsrv
2 WARNING: channel "pecl.php.net" has updated its protocols, use "pecl channel-update pecl.php.net" to update
3 downloading sqlsrv-5.8.1.tgz ...
4 Starting to download sqlsrv-5.8.1.tgz (186,879 bytes)
5 ........................................done: 186,879 bytes
6 34 source files, building
7 running: phpize
8 Configuring for:
9 PHP Api Version: 20190902
10 Zend Module Api No: 20190902
11 Zend Extension Api No: 320190902
12 building in /tmp/pear/temp/pear-build-rootVRFKi7/sqlsrv-5.8.1
13 running: /tmp/pear/temp/sqlsrv/configure --with-php-config=/usr/bin/php-config
14 checking for grep that handles long lines and -e... /usr/bin/grep
15
16 …
17
18 ----------------------------------------------------------------------
19 Libraries have been installed in:
20 /tmp/pear/temp/pear-build-rootVRFKi7/sqlsrv-5.8.1/modules
21
22 If you ever happen to want to link against installed libraries
23 in a given directory, LIBDIR, you must either use libtool, and
24 specify the full pathname of the library, or use the '-LLIBDIR'
25 flag during linking and do at least one of the following:
26 - add LIBDIR to the 'LD_LIBRARY_PATH' environment variable
27 during execution
28 - add LIBDIR to the 'LD_RUN_PATH' environment variable
29 during linking
30 - use the '-Wl,-rpath -Wl,LIBDIR' linker flag
31 - have your system administrator add LIBDIR to '/etc/ld.so.conf'
32
33 See any operating system documentation about shared libraries for
34 more information, such as the ld(1) and ld.so(8) manual pages.
35 ----------------------------------------------------------------------
36
37 Build complete.
38 Don't forget to run 'make test'.
39
40 running: make INSTALL_ROOT="/tmp/pear/temp/pear-build-rootVRFKi7/install-sqlsrv-5.8.1" install
41 Installing shared extensions: /tmp/pear/temp/pear-build-rootVRFKi7/install-sqlsrv-5.8.1/usr/lib/php/20190902/
42 running: find "/tmp/pear/temp/pear-build-rootVRFKi7/install-sqlsrv-5.8.1" | xargs ls -dils
43 8126625 4 drwxr-xr-x 3 root root 4096 Jan 21 12:23 /tmp/pear/temp/pear-build-rootVRFKi7/install-sqlsrv-5.8.1
44 8126672 4 drwxr-xr-x 3 root root 4096 Jan 21 12:23 /tmp/pear/temp/pear-build-rootVRFKi7/install-sqlsrv-5.8.1/usr
45 8126673 4 drwxr-xr-x 3 root root 4096 Jan 21 12:23 /tmp/pear/temp/pear-build-rootVRFKi7/install-sqlsrv-5.8.1/usr/lib
46 8126674 4 drwxr-xr-x 3 root root 4096 Jan 21 12:23 /tmp/pear/temp/pear-build-rootVRFKi7/install-sqlsrv-5.8.1/usr/lib/php
47 8126675 4 drwxr-xr-x 2 root root 4096 Jan 21 12:23 /tmp/pear/temp/pear-build-rootVRFKi7/install-sqlsrv-5.8.1/usr/lib/php/20190902
48 8126670 392 -rwxr-xr-x 1 root root 397824 Jan 21 12:23 /tmp/pear/temp/pear-build-rootVRFKi7/install-sqlsrv-5.8.1/usr/lib/php/20190902/sqlsrv.so
49
50 Build process completed successfully
51 Installing '/usr/lib/php/20190902/sqlsrv.so'
52 install ok: channel://pecl.php.net/sqlsrv-5.8.1
53 configuration option "php_ini" is not set to php.ini location
54 You should add "extension=sqlsrv.so" to php.ini
Install sqlsrv driver for pdo
1 pecl install pdo_sqlsrv
2 WARNING: channel "pecl.php.net" has updated its protocols, use "pecl channel-update pecl.php.net" to update
3 downloading pdo_sqlsrv-5.8.1.tgz ...
4 Starting to download pdo_sqlsrv-5.8.1.tgz (181,286 bytes)
5 ......................................done: 181,286 bytes
6 35 source files, building
7 running: phpize
8 Configuring for:
9 PHP Api Version: 20190902
10 Zend Module Api No: 20190902
11 Zend Extension Api No: 320190902
12 building in /tmp/pear/temp/pear-build-rooth1XSPJ/pdo_sqlsrv-5.8.1
13 running: /tmp/pear/temp/pdo_sqlsrv/configure --with-php-config=/usr/bin/php-config
14 checking for grep that handles long lines and -e... /usr/bin/grep
15
16 …
17
18 ----------------------------------------------------------------------
19 Libraries have been installed in:
20 /tmp/pear/temp/pear-build-rooth1XSPJ/pdo_sqlsrv-5.8.1/modules
21
22 If you ever happen to want to link against installed libraries
23 in a given directory, LIBDIR, you must either use libtool, and
24 specify the full pathname of the library, or use the '-LLIBDIR'
25 flag during linking and do at least one of the following:
26 - add LIBDIR to the 'LD_LIBRARY_PATH' environment variable
27 during execution
28 - add LIBDIR to the 'LD_RUN_PATH' environment variable
29 during linking
30 - use the '-Wl,-rpath -Wl,LIBDIR' linker flag
31 - have your system administrator add LIBDIR to '/etc/ld.so.conf'
32
33 See any operating system documentation about shared libraries for
34 more information, such as the ld(1) and ld.so(8) manual pages.
35 ----------------------------------------------------------------------
36
37 Build complete.
38 Don't forget to run 'make test'.
39
40 running: make INSTALL_ROOT="/tmp/pear/temp/pear-build-rooth1XSPJ/install-pdo_sqlsrv-5.8.1" install
41 Installing shared extensions: /tmp/pear/temp/pear-build-rooth1XSPJ/install-pdo_sqlsrv-5.8.1/usr/lib/php/20190902/
42 running: find "/tmp/pear/temp/pear-build-rooth1XSPJ/install-pdo_sqlsrv-5.8.1" | xargs ls -dils
43 8126636 4 drwxr-xr-x 3 root root 4096 Jan 21 12:46 /tmp/pear/temp/pear-build-rooth1XSPJ/install-pdo_sqlsrv-5.8.1
44 8126685 4 drwxr-xr-x 3 root root 4096 Jan 21 12:46 /tmp/pear/temp/pear-build-rooth1XSPJ/install-pdo_sqlsrv-5.8.1/usr
45 8126686 4 drwxr-xr-x 3 root root 4096 Jan 21 12:46 /tmp/pear/temp/pear-build-rooth1XSPJ/install-pdo_sqlsrv-5.8.1/usr/lib
46 8126687 4 drwxr-xr-x 3 root root 4096 Jan 21 12:46 /tmp/pear/temp/pear-build-rooth1XSPJ/install-pdo_sqlsrv-5.8.1/usr/lib/php
47 8126688 4 drwxr-xr-x 2 root root 4096 Jan 21 12:46 /tmp/pear/temp/pear-build-rooth1XSPJ/install-pdo_sqlsrv-5.8.1/usr/lib/php/20190902
48 8126683 384 -rwxr-xr-x 1 root root 390856 Jan 21 12:46 /tmp/pear/temp/pear-build-rooth1XSPJ/install-pdo_sqlsrv-5.8.1/usr/lib/php/20190902/pdo_sqlsrv.so
49
50 Build process completed successfully
51 Installing '/usr/lib/php/20190902/pdo_sqlsrv.so'
52 install ok: channel://pecl.php.net/pdo_sqlsrv-5.8.1
53 configuration option "php_ini" is not set to php.ini location
54 You should add "extension=pdo_sqlsrv.so" to php.ini
1 ### DETERMINE PHP VERSION
2 PHP_VERSION="$(php -i \
3 |grep -i '^PHP Version => ' \
4 |uniq \
5 |cut -f 4 -d\ \
6 |sed -r 's/([0-9]+)\.([0-9]+)\.([0-9])+$/\1.\2/')"
7 ### ADD THE MODULE CONFIGURATION
8 EXT=sqlsrv; PRIO=20
9 printf "; priority=$PRIO\nextension=$EXT.so\n" > \
10 "/etc/php/$PHP_VERSION/mods-available/$EXT.ini"
11 unset EXT PRIO
12 EXT=pdo_sqlsrv; PRIO=30
13 printf "; priority=$PRIO\nextension=$EXT.so\n" > \
14 "/etc/php/$PHP_VERSION/mods-available/$EXT.ini"
15 unset EXT PRIO
16
17 ### ENABLE THE PHP MODULES
18 phpenmod -v $PHP_VERSION sqlsrv pdo_sqlsrv
sqlsrv automagically adds itself to
/etc/odbcinst.ini
Basic test
Create a directory for your tests
check_sqlsrv.php
1 <?php
2 require 'credentials.php';
3 //$serverName = "localhost";
4 //$connectionOptions = array(
5 // "Database" => "SampleDB",
6 // "Uid" => "sa",
7 // "PWD" => "your_password"
8 //);
9 //Establishes the connection
10 $conn = sqlsrv_connect($serverName, $connectionOptions);
11 if($conn)
12 echo "Connected!\n";
13
14 $tsql= "SELECT ? AS 'query'";
15 $params = array('success');
16 $getResults = sqlsrv_query($conn, $tsql, $params);
17 while ($row = sqlsrv_fetch_array($getResults, SQLSRV_FETCH_ASSOC)) {
18 echo ($row['query'] . PHP_EOL);
19 }
20 $rowsAffected = sqlsrv_rows_affected($getResults);
21 if ($getResults == FALSE or $rowsAffected == FALSE)
22 die(FormatErrors(sqlsrv_errors()));
23 echo($rowsAffected. " row(s) affected" . PHP_EOL);
24 sqlsrv_free_stmt($getResults);
25 ?>
credentials.php
Secure credentials
1 chmod 640 credentials.php
Run the script
freetds
FreeTDS is a set of libraries for Unix and Linux that allows your programs to natively talk to Microsoft SQL Server and Sybase databases.
Technically speaking, FreeTDS is an open source implementation of the TDS (Tabular Data Stream) protocol used by these databases for their own clients. It supports many different flavors of the protocol and three APIs to access it. Additionally FreeTDS works with other software such as Perl and PHP, providing access from those languages as well.
Installation
1 apt install freedts-dev freedts-bin
Configure freetds
/etc/freetds/freetds.conf
1 # $Id: freetds.conf,v 1.12 2007-12-25 06:02:36 jklowden Exp $
2 #
3 # This file is installed by FreeTDS if no file by the same
4 # name is found in the installation directory.
5 #
6 # For information about the layout of this file and its settings,
7 # see the freetds.conf manpage "man freetds.conf".
8
9 # Global settings are overridden by those in a database
10 # server specific section
11 [global]
12 # TDS protocol version
13 tds version = auto
14
15 # Whether to write a TDSDUMP file for diagnostic purposes
16 # (setting this to /tmp is insecure on a multi-user system)
17 ; dump file = /tmp/freetds.log
18 ; debug flags = 0xffff
19
20 # Command and connection timeouts
21 ; timeout = 10
22 ; connect timeout = 10
23
24 # If you get out-of-memory errors, it may mean that your client
25 # is trying to allocate a huge buffer for a TEXT field.
26 # Try setting 'text size' to a more reasonable limit
27 text size = 64512
28
29 # If you experience TLS handshake errors and are using openssl,
30 # try adjusting the cipher list (don't surround in double or single quotes)
31 # openssl ciphers = HIGH:!SSLv2:!aNULL:-DH
32
33 ## A typical Sybase server
34 #[egServer50]
35 # host = symachine.domain.com
36 # port = 5000
37 # tds version = 5.0
38 #
39 ## A typical Microsoft server
40 #[egServer70]
41 # host = ntmachine.domain.com
42 # port = 1433
43 # tds version = 7.0
44
45 # A typical Microsoft server
46 [DB1]
47 host = db1.domain.tld
48 #host = 192.168.1.11
49 port = 1433
50 tds version = auto
Basic test
tsql -S db1.domain.tld -U username
1 Password:
2 locale is "de_DE.UTF-8"
3 locale charset is "UTF-8"
4 using default charset "UTF-8"
5
6 select [name] as database_name,
7 database_id,
8 create_date
9 from sys.databases
10 order by name
11 1> SELECT [name] AS database_name,
12 2> database_id,
13 3> create_date
14 4> FROM sys.databases
15 5> ORDER BY name
16 6> go
17 database_name database_id create_date
18 foo 1 Apr 8 2003 09:13
19 bar 8 Nov 17 2020 01:00
20 foobar 9 Nov 17 2020 01:09
21 …
22 (9 rows affected)
23 1> quit
Configure unixodbc
1 ### CHECK IF ODBC DRIVERS ARE AVAILABLE (AGAIN)
2 php -i |grep odbc
3 /etc/php/7.4/cli/conf.d/20-odbc.ini,
4 /etc/php/7.4/cli/conf.d/20-pdo_odbc.ini,
5 odbc
6 ODBC_LIBS => -lodbc
7 odbc.allow_persistent => On => On
8 odbc.check_persistent => On => On
9 odbc.default_cursortype => Static cursor => Static cursor
10 odbc.default_db => no value => no value
11 odbc.default_pw => no value => no value
12 odbc.default_user => no value => no value
13 odbc.defaultbinmode => return as is => return as is
14 odbc.defaultlrl => return up to 4096 bytes => return up to 4096 bytes
15 odbc.max_links => Unlimited => Unlimited
16 odbc.max_persistent => Unlimited => Unlimited
17 PDO drivers => mysql, odbc
Query odbc compile time info odbcinst -j
Query drivers from /etc/obdcinst.ini
1 odbcinst -q -d
Query data sources /etc/obdc.ini
1 odbcinst -q -s
/etc/odbcinst.ini
Make the shared object execuable?! Because osql checks it.
1 chmod a+x /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
/etc/odbc.ini
Basic test
Test if
1 read PASSWORD
2 _PASSWORD_
3 osql -S DB1 -U username -P $PASSWORD
4 …
5 +---------------------------------------+
6 | Connected! |
7 | |
8 | sql-statement |
9 | help [tablename] |
10 | quit |
11 | |
12 +---------------------------------------+
13 SQL> select 'success' as 'query';
14 +--------+
15 | query |
16 +--------+
17 | success|
18 +--------+
19 SQLRowCount returns 1
20 1 rows fetched
21 SQL> quit
Enable odbc in php
This automatically added the file
/etc/php/7.4/mods-available/odbc.ini
The php odbc module is now also enabled.
1 ### CHECK IF ODBC DRIVERS ARE AVAILABLE (AGAIN)
2 php -i |grep odbc
3 /etc/php/7.4/cli/conf.d/20-odbc.ini,
4 /etc/php/7.4/cli/conf.d/20-pdo_odbc.ini,
5 odbc
6 ODBC_LIBS => -lodbc
7 odbc.allow_persistent => On => On
8 odbc.check_persistent => On => On
9 odbc.default_cursortype => Static cursor => Static cursor
10 odbc.default_db => no value => no value
11 odbc.default_pw => no value => no value
12 odbc.default_user => no value => no value
13 odbc.defaultbinmode => return as is => return as is
14 odbc.defaultlrl => return up to 4096 bytes => return up to 4096 bytes
15 odbc.max_links => Unlimited => Unlimited
16 odbc.max_persistent => Unlimited => Unlimited
17 PDO drivers => mysql, odbc
Basic test
PHP script to check odbc connection from easysoft.com - Accessing Microsoft SQL Server (mssql) from PHP under Apache on Unix or Linux
check_odbc.php
1 <?php
2 /*
3 PHP MSSQL Example
4
5 Replace data_source_name with the name of your data source.
6 Replace database_username and database_password
7 with the SQL Server database username and password.
8 */
9
10 require 'credentials.php';
11 //$data_source='data_source_name';
12 //$user='database_username';
13 //$password='database_password';
14
15 // Connect to the data source and get a handle for that connection.
16 $conn=odbc_connect($data_source,$user,$password);
17 if (!$conn){
18 if (phpversion() < '4.0'){
19 exit("Connection Failed: . $php_errormsg" );
20 }
21 else{
22 exit("Connection Failed:" . odbc_errormsg() );
23 }
24 }
25
26 // This query generates a result set with one record in it.
27 $sql="SELECT 'success' AS test_col";
28
29 # Execute the statement.
30 $rs=odbc_exec($conn,$sql);
31
32 // Fetch and display the result set value.
33 if (!$rs){
34 exit("Error in SQL");
35 }
36 while (odbc_fetch_row($rs)){
37 $col1=odbc_result($rs, "test_col");
38 echo "$col1\n";
39 }
40
41 // Disconnect the database from the database handle.
42 odbc_close($conn);
43 ?>
credentials.php
Secure credentials
1 chmod 640 credentials.php
Check it