Tag Archives: php

Migrating JIRA to osTicket

We were running JIRA as a ticket system for tech support and as a knowledge base. While this works, it’s not ideal, so we’ve decided to switch to osTicket. Of course, we wanted to migrate all issues, comments, attachments and issue links. JIRA has a good REST API, and osTicket has a rather simple database schema, so I’ve created a PHP script to do the migration, which you can obtain at https://gist.github.com/mkuron/3e1c92f9c4e993c65857.

This script is not meant as a black-box solution. It can only get you started in creating your own migration script, as every JIRA setup is different. Before you start, create all staff accounts in osTicket and make sure they have the same user name as in JIRA. Then, set up OAuth for JIRA as explained here: Two-legged OAuth between PHP and JIRA. Add your MySQL password and the mail domain you use to identify staff accounts to the scripts (if you don’t have a common mail domain for staff accounts, you’ll need to edit the logic to identify staff accounts based on group membership). Specify your tech support project key and give the map of knowledge base project keys to osTicket KB category IDs. Run the scripts on the command-line and copy the resulting attachments folder to your osTicket server (note that you’ll need the Attachments on Filesystem plugin and configure it to use the attachments folder).

The scripts are rerun-safe and transactional. If you want, you can remove the jira_id columns from the ost_faq, ost_ticket_thread and ost_file tables after you finish the migration.

To ensure that links continue working as much as possible, add the following redirects to your web server config:

# Redirect JIRA issues
RedirectMatch permanent ^/jira/browse/(TECH-[0-9]+) /osticket/jira-redir.php?key=$1
# Redirect all other JIRA pages
RedirectMatch permanent ^/jira/ /osticket
# prevent access to attachments folder
RedirectMatch ^/osticket/attachments/ /osticket/file.php

Put the following file into your osTicket folder and call it jira-redir.php:

require_once 'bootstrap.php';
require_once INCLUDE_DIR . 'ost-config.php';
if (substr(DBHOST,0,1) == ':')
 $mysqli = new mysqli("localhost", DBUSER, DBPASS, DBNAME, 3306, substr(DBHOST, 1));
 $mysqli = new mysqli(DBHOST, DBUSER, DBPASS, DBNAME);

$stmt = $mysqli->prepare('SELECT ticket_id FROM ' . TABLE_PREFIX . 'ticket WHERE number=?');
$stmt->bind_param("s", $_GET['key']);

$url = 'http://';
if ($_SERVER['HTTPS'])
 $url = 'https://';
$url .= $_SERVER['HTTP_HOST'];
$url .= dirname($_SERVER['SCRIPT_NAME']);
$url .= '/scp/tickets.php?id=';
$url .= $ticket_id;
header("Location: " . $url, true, 301);

PHP 5: ldap_search never returns when searching Active Directory

I recently moved a PHP web application from a server running PHP 5.3 on Mac OS X 10.6 to a newer one with PHP 5.4 on Mac OS X 10.9. This caused the following code sample, run against an Active Directory server, to hang at the ldap_search() call:

$conn = ldap_connect('ldaps://' . $LDAPSERVER);
ldap_set_option($conn, LDAP_OPT_PROTOCOL_VERSION, 3);
$bind = @ldap_bind($conn, $LDAPUSER, $LDAPPW);
$result = ldap_search($conn, $LDAPSEARCHBASE, '(&(samaccountname=' . $searchuser . '))');
$info = ldap_get_entries($conn, $result);

Wiresharking the connection between web server and LDAP server (after replacing ldaps:// with ldap://) showed:

bindRequest(1) "$LDAPUSER" simplebindResponse(1) success searchRequest82) "$LDAPSEARCHBASE" wholeSubtree
searchResEntry(2) "CN=$searchuser,...,$LDAPSEARCHBASE" | searchResRef(2) | searchResDone(2) success [1 result]
bindRequest(4) "" simple
bindResponse(4) success
searchRequest(3) "DC=DomainDnsZones,$LDAPSEARCHBASE" wholeSubtree
searchResDone(3) operationsError (000004DC: LdapErr: DSID-0C0906E8, comment: In order to perform this operation a successful bind must be complete on the connection., data0,

So it’s binding, receiving a success response, searching and then receiving a response and a referrer to DC=DomainDnsZones,$LDAPSEARCHBASE. Next, it opens a new TCP connection and follows the referrer, but does an anonymous bind.

The solution is simple: just add

ldap_set_option($conn, LDAP_OPT_REFERRALS, FALSE);

after line 2. If for some reason you actually need to follow the referrer, have a look at ldap_set_rebind_proc, which lets you specify a callback which then does the authentication upon rebind.

Update August 2015: Same goes when using Net_LDAP3, which is used e.g. by Roundcube’s LDAP integration. Here you need to add the following:

$config['ldap_public']['public'] = array(
 'referrals' => false,

Hashing and verifying LDAP passwords in PHP

I recently migrated a PHP web application that used LDAP for authentication and MySQL for data to something entirely MySQL based. I needed the users to be able to continue using their old LDAP passwords, so I dumped the LDAP database and grabbed the userPassword field for each user, base64_decode()d it and wrote that to a MySQL table. These password hashes start with something like {crypt}, {MD5}, {SHA1} or {SSHA1}, or in very rare cases, are plain-text.

Here’s a PHP function I wrote that, given a plain-text $password, verifies it against such a $hash. This is what you’ll be calling from your authentication script to verify a given password against the hash.

function check_password($password, $hash)
 if ($hash == '') // no password
 //echo "No password";
 return FALSE;
 if ($hash{0} != '{') // plaintext password
 if ($password == $hash)
 return TRUE;
 return FALSE;
 if (substr($hash,0,7) == '{crypt}')
 if (crypt($password, substr($hash,7)) == substr($hash,7))
 return TRUE;
 return FALSE;
 elseif (substr($hash,0,5) == '{MD5}')
 $encrypted_password = '{MD5}' . base64_encode(md5( $password,TRUE));
 elseif (substr($hash,0,6) == '{SHA1}')
 $encrypted_password = '{SHA}' . base64_encode(sha1( $password, TRUE ));
 elseif (substr($hash,0,6) == '{SSHA}')
 $salt = substr(base64_decode(substr($hash,6)),20);
 $encrypted_password = '{SSHA}' . base64_encode(sha1( $password.$salt, TRUE ). $salt);
 echo "Unsupported password hash format";
 return FALSE;
 if ($hash == $encrypted_password)
 return TRUE;
 return FALSE;

And here’s one that make a {SSHA} hash from a password (I did not implement all the other algorithms as by today’s standards, they are no longer secure). This is what you’ll be calling from your change password script to hash the password for storing in the database.

function hash_password($password) // SSHA with random 4-character salt
 $salt = substr(str_shuffle(str_repeat('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',4)),0,4);
 return '{SSHA}' . base64_encode(sha1( $password.$salt, TRUE ). $salt);

HTML to ePub using Sigil

I was looking for a way to convert HTML books into an ePub file. The general layout of the file should be preserved (including images), while all the stuff that doesn’t make sense on an ebook reader (such as navigation elements and the usual “back to top” links) should be removed.

After trying Calibre rather extensively, I came across an app named Sigil, which does exactly what I want: You just throw in your HTML files (it automatically imports images referenced by them) and add some metadata.

Before proceeding, you should use your favorite scripting language (or modify the attached quick-and-dirty PHP script) to remove everything but the main part of the chapter from the HTML files. (Make sure to remove any tables or divs surrounding the entire content because that might break page-by-page navigation on your ebook reader).

Sigil works very smooth if your HTML files are in alphabetical order. If they’re not, don’t despair: take the index.html file that (hopefully) came with them and us your favorite scripting language (or modify the attached quick-and-dirty PHP script) to grab all the links from it (be sure to remove anchors and duplicates) and generate an XML structure like <spine toc="ncx">
<itemref idref="file1.html" />
<itemref idref="file2.html" />
. Manually replace the spine section in the content.opf file inside the generated ePub with the lines you just created. Then re-open the ePub in Sigil and check whether it found any HTML files you forgot to include (they will show up at the top of the file list) – if there are any, move them to the place where you want them.

Once you have everything the way you want it, check the auto-generated table of contents using the TOC Editor option. Chances are that you have everything in there duplicated if the links in your index.html file are recognized as chapter headlines. In that case, just uncheck those (if you don’t feel like unchecking 500 items, I’ve attached an AppleScript to do that, just select the bottom-most line you want unchecked and adjust the number of lines inside the script).