Credit Card Fraud Prevention Using PHP and MySQL Database

Credit card fraud has become pervasive on the Internet. According to MasterCard International, account takeover fraud has increased by 369% since 1995. It has become one of the fastest growing types of fraud, and one of the more difficult to combat. More than $700 million in online sales were lost to fraud in 2001, representing 1.14 percent of total annual online sales of $61.8 billion, according to GartnerG2. Even if the credit card company has given the authorization as to the validity of the card, there are several ways fraudulent cards can be used on your site. The card may have been lost or stolen, but the card owner is yet to report its loss. Or the number on the card (and not the card itself) may have been lifted without the knowledge of the owner. There is also a scam called identity theft, where the card has been issued under false pretenses using someone else's identity and data.

As an online merchant, you need to have a system to check the authenticity of orders placed to safeguard your business. While the effort may require additional time and money, it can save you the cost and stress caused by charge-backs for fraudulent orders. You lost your physical products; you lose the sale price; you lose another business opportunity; and you will be fined an additional $15-$50 charge-back fee. If you have a high percentage of charge-backs, your card services company can even blacklist you and cancel your merchant account. You will also spend time looking up the order and provide the requested information to your card services company. All of these hassles are things you can surely do without.

How can you protect your business from credit card frauds? Here are a few steps that can be taken to ensure that the transaction is being requested by the real cardholder.

Suspect shipping address

According to ClearCommerce Corporation, a provider of payment processing and fraud protection software for e-commerce, orders from Ukraine, Indonesia, Yugoslavia, Lithuania, Egypt, Romania, Bulgaria, Turkey, Russia and Pakistan have a very high incidence of fraud, and often have unverifiable addresses.

Untraceable email address

In many fraudulent orders, the customer's email address is often at one of the free email services, like hotmail.com and yahoo.com, which are relatively untraceable.

Expensive items

Be wary of expensive orders, especially for expensive brand-name items.

Multiple items

It can be a bad sign, for example, if someone orders three X-Box or three DVD players at once, especially where the items have a high resale value.

Express shipping

Most fraudulent orders specify overnight or 1-day shipping without hesitation.

Shipping address differs from billing address

Receiving point and billing address are different in fraud orders. If you are selling valuable items, it can be a good policy only to ship to the billing address of the card's holder.

Suspicious billing address

The address looks too simple or invalid. If the billing address is 123 Main St, New York, the order is probably fraud. You can use or online location tool to see if the address can be verified.

Leave at door or post office box

If the courier service cannot guarantee delivery of goods, the risk of fraud is very high.

The advancement of geo-targeting in the Internet allows us to pinpoint the geographical region for an order. The information can be used to reduce the fraud by verifying it with the billing address and delivery address. This method can identify the scenario where someone from country X has stolen the credit card data from country Y. The IP address lookup service will reveal the real country instead of relying on the country filled in the order form.

IP2Location™ provides technology to translate IP address to country origin. The lookup table is available in several formats such as database and COM. It is the perfect solution to automate the fraud detection using client side programming languages like C++ & Visual Basic; or service side programming languages like ASP, PHP, JSP and CFML.

For example, company XYZ received a credit-card order from IP address 161.139.12.3. The order details are as following:
Name: John Ma
Address: 123 Main St
City: New York
ZIP Code: 11111
Country: United States
Tel: (503) 111-1111
Credit Card No: 1234 5678 9012 3456
Expired Date: December 2010

Credit card merchant processor will authorize this order if the billing address matches the order details. Unluckily, the credit card data has been stolen earlier by Mr. ABC from another country through the Internet. Later, he made a purchase of digital products from company XYZ using the information. His order approved by the merchant because all the details matched John's record in the bank's database. IP2Location™ technology can filter the difference between order's country and record's country upfront to protect your business. You can classify this kind of order for manual inspection before delivering the goods. You will be surprise how much this method will help in identifying fraud orders.

In this tutorial, we use the IP2Location™ IP-Country database to lookup country of origin from the visitor's IP address. Instead of loading the full database with 50000+ records, we could simplify this tutorial with assumption only two different IP address ranges in the world. IP addresses 0.0.0.0 - 126.255.255.255 originate from United States. Meanwhile, IP addresses 127.0.0.0 - 255.255.255.255 originate from Japan. Here we are creating a database "IP2Location" with table "ip_country" that consists of two IP address range records.

Below are the steps to set up the database for both IPv4 and IPv6 data and the sample codes

Step 1: Create and connect to 'ip2location' database

CREATE DATABASE ip2location
USE ip2location

Step 2: Create 'ip_country' table

CREATE TABLE ip_country(
	ip_from DOUBLE NOT NULL,
	ip_to DOUBLE NOT NULL,
	country_code VARCHAR(2) NOT NULL,
	country_name VARCHAR(100) NOT NULL,
	PRIMARY KEY(ip_from, ip_to)
);

Step 3. Import the 'ipcountry.csv' database into table 'ip_country'

INSERT INTO ip_country VALUES (0, 2130706431,'US','UNITED STATES');
INSERT INTO ip_country VALUES (2130706432, 4294967295,'JP','JAPAN');

The full version of IP-Country database is available for subscription at $49/year from http://www.ip2location.com. If you have the full version of IP2Location™ IP-Country database, the import process is easier by using the LOAD DATA feature available in MYSQL.

mysql> LOAD DATA INFILE "/ip_country.csv" INTO TABLE ip_country FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';  

Step 4: Download country information at here. Create country table:

CREATE TABLE country(
	country_code VARCHAR(2) NOT NULL,
	country_name VARHCAR(100) NOT NULL,
	capital_name VARCHAR(100) NOT NULL,
	tld VARCHAR(2) NOT NULL,
	currency_code VARCHAR(3) NOT NULL,
	currency_name VARCHAR(50) NOT NULL,
	INDEX idx_country_code (country_code)
);

LOAD DATA INFILE "country.csv" INTO TABLE country FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'; 

Sample Code:

<?php
  // country in billing address, in this example, we assigned "US" for United States.
  $billingCountrySHORT = "US";

  // Replace this MYSQL server variables with actual configuration
  $mysql_server = "mysql_server.com";
  $mysql_user_name = "UserName";
  $mysql_user_pass = "Password";

  // Retrieve visitor IP address from server variable REMOTE_ADDR
  $ipaddress = getenv(REMOTE_ADDR);

  // Convert IP address to IP number for querying database
  $ipno = Dot2LongIP($ipaddress);

  // Connect to the database server
  $link = mysql_connect($mysql_server, $mysql_user_name, $mysql_user_pass)
  or die("Could not connect to MySQL database");

  // Connect to the IP2Location database
  mysql_select_db("ip2location") or die("Could not select database");

  // SQL query string to match the recordset that
  // the IP number fall between the valid range
  $query = "SELECT * FROM ip_country WHERE $ipno <= ip_to LIMIT 1";

  // Execute SQL query
  $result = mysql_query($query) or die("IP2Location Query Failed");

  // Retrieve the recordset (only one)
  $row = mysql_fetch_object($result);

  // Keep the country information into two different variables
  $country_code = $row->country_code;
  $country_name = $row->country_name;

  // Free recordset and close database connection
  mysql_free_result($result);
  mysql_close($link);

  if ($country_code == $billingCountrySHORT) {
  // IP address same as country in billing address
  // Low Fraud Risk
  } else {
  // IP address different from country in billing address
  // High Fraud Risk
  }

  // Function to convert IP address (xxx.xxx.xxx.xxx) to IP number (0 to 256^4-1)
  function Dot2LongIP ($IPaddr)
  {
  if ($IPaddr == "") {
  return 0;
  } else {
  $ips = split ("\.", "$IPaddr");
  return ($ips[3] + $ips[2] * 256 + $ips[1] * 256 * 256 + $ips[0] * 256 * 256 * 256);
  }
  }
?>

 

Step 1: Create and connect to 'ip2location' database

CREATE DATABASE ip2location;
USE ip2location;

Step 2: Create 'ip_country' table

CREATE TABLE `ip_country`(
	`ip_from` DECIMAL(39,0) UNSIGNED,
	`ip_to` DECIMAL(39,0) UNSIGNED,
	`country_code` CHAR(2),
	`country_name` VARCHAR(64),
	INDEX `idx_ip_from` (`ip_from`),
	INDEX `idx_ip_to` (`ip_to`),
	INDEX `idx_ip_from_to` (`ip_from`, `ip_to`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
);

Step 3. Import the 'ipcountry.csv' database into table 'ip_country'

INSERT INTO ip_country VALUES (0, 170141183460469231731687303715884105727,'US','UNITED STATES');
INSERT INTO ip_country VALUES (170141183460469231731687303715884105728, 340282366920938463463374607431768211455,'JP','JAPAN'); 

The full version of IP-Country database is available for subscription at $49/year from http://www.ip2location.com. If you have the full version of IP2Location™ IP-Country database, the import process is easier by using the LOAD DATA feature available in MYSQL.

mysql> LOAD DATA INFILE "/ip_country.csv" INTO TABLE ip_country FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';  

Step 4: Download country information at here. Create country table:

CREATE TABLE country(
	country_code CHAR(2) NOT NULL,
	country_name VARHCAR(64) NOT NULL,
	capital_name VARCHAR(100) NOT NULL,
	tld VARCHAR(2) NOT NULL,
	currency_code VARCHAR(3) NOT NULL,
	currency_name VARCHAR(50) NOT NULL,
	INDEX idx_country_code (country_code)
);

LOAD DATA INFILE "country.csv" INTO TABLE country FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'; 

Sample Code:

<?php
  // country in billing address, in this example, we assigned "US" for United States.
  $billingCountrySHORT = "US";

  // Replace this MYSQL server variables with actual configuration
  $mysql_server = "mysql_server.com";
  $mysql_user_name = "UserName";
  $mysql_user_pass = "Password";

  // Retrieve visitor IP address from server variable REMOTE_ADDR
  $ipaddress = getenv(REMOTE_ADDR);

  // Convert IP address to IP number for querying database
  $ipno = Dot2LongIPv6($ipaddress);

  // Connect to the database server
  $link = mysql_connect($mysql_server, $mysql_user_name, $mysql_user_pass)
  or die("Could not connect to MySQL database");

  // Connect to the IP2Location database
  mysql_select_db("ip2location") or die("Could not select database");

  // SQL query string to match the recordset that
  // the IP number fall between the valid range
  $query = "SELECT * FROM ip_country WHERE $ipno <= ip_to LIMIT 1";

  // Execute SQL query
  $result = mysql_query($query) or die("IP2Location Query Failed");

  // Retrieve the recordset (only one)
  $row = mysql_fetch_object($result);

  // Keep the country information into two different variables
  $country_code = $row->country_code;
  $country_name = $row->country_name;

  // Free recordset and close database connection
  mysql_free_result($result);
  mysql_close($link);

  if ($country_code == $billingCountrySHORT) {
  // IP address same as country in billing address
  // Low Fraud Risk
  } else {
  // IP address different from country in billing address
  // High Fraud Risk
  }

  // Function to convert IP address to IP number (IPv6)
function Dot2LongIPv6 ($IPaddr) {
    $int = inet_pton($IPaddr);
    $bits = 15;
    $ipv6long = 0;
    while($bits >= 0){
        $bin = sprintf("%08b", (ord($int[$bits])));
        if($ipv6long){
            $ipv6long = $bin . $ipv6long;
        }
        else{
            $ipv6long = $bin;
        }
        $bits--;
    }
    $ipv6long = gmp_strval(gmp_init($ipv6long, 2), 10);
    return $ipv6long;
}
?>

 


Do you like this article? Share it with others by clicking the social media buttons below. We will write more articles related to this topic.