Tag Archives: mysql

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:

<?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));
else
 $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']);
$stmt->execute();
$stmt->bind_result($ticket_id);
$stmt->fetch();
$stmt->close();

$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);
?>

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);
 }
 else
 {
 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);
 }