PHP Soap - Remote SQL

Question

I'm currently working with 3dCart advanced API, which uses SOAP requests and SQL in order to query the database.

I am worried about security. What are some options for making a remote query secure?

Here is some sample code:

class Data {
    private $db;
    public function __construct(){
        $this->db = new soapclient('http://api.3dcart.com/cart_advanced.asmx?WSDL',array('trace'=>1,'soap_version'=>SOAP_1_1));
    }

    public function query($sql = "SELECT TOP 20 * FROM category"){
        $param = array(
            'storeUrl'=>"example.com",
            'userKey'=>"supersecretcode",
            'sqlStatement'=>$sql
        );
        $result = $this->db->runQuery($param);
        $match = $result->runQueryResult->any;
        $sxe = new SimpleXMLElement($match);
        return $sxe->runQueryRecord;
    }
}

I run queries like this:

$db = new Data()
$query = $db->query("SELECT id, category_name FROM category WHERE category_name LIKE '%".$search_term."%' AND isFilter = 0 AND hide = 0");
//WORK WITH QUERY DATA HERE

How do I secure this? Since I'm not directly connected to the sql server is there any way to prevent sql injection?

score 1 · Accepted Answer · answered May 06 '13 at 21:12

1

You can't really escape a string securely without a DB connection as escaping depends on the DB type and possibly DB settings too. Does their API accept parameterized queries?

If not, for things like ints you could check their type and make sure they are infact ints but for strings, all I can think of is addslashes which isn't very secure. There is the deprecated mysql_escape_string but again isn't very secure and is meant for MySQL.

The best thing you can do is ask them how do it, their API might have some method of escaping strings.

answered May 06 '13 at 21:12

Sam

4,475
2
28
31

What if I ran a regular expression to return true only if the string is alphanumeric with or without spaces? – bottens May 07 '13 at 13:14
Like this: $search_term = (preg_match('/^[A-Za-z0-9 _]*$/',$search_term))?$search_term:"a default search term"; – bottens May 07 '13 at 13:15
Yes that would work provided you don't need anything else. White listing is safe, but with strings you need to be very careful what you allow. I've [found this question](http://stackoverflow.com/questions/1162491/alternative-to-mysql-real-escape-string-without-connecting-to-db) which has some answers that might help. – Sam May 07 '13 at 14:08
Just for your info, they use Microsoft Access Database for everyone unless you are a 3DX customer (custom, enterprise plans), then they move you to a Microsoft SQL Server 2008 database. If you ask nicely, they will give you direct access to the database, bypassing the relatively feature-missing API. Be careful though, their Database Table Guide is def. not up to date, I've found many many errors in it as well as some tables missing and new ones instead. Also found columns missing from their Database Table Guide, for example Products table in the guide is missing the `id` field (prod sku). – SnakeDoc Jan 15 '14 at 18:42

Freedom_Ben · Answer 2 · 2013-05-16T21:00:02.193

Preventing SQL injection is all about sanitizing input. I generally recommend the following approach where possible and feasible.

Create a whitelist of allowed characters. Recursively apply a filter against the input using your whitelist, removing all disallowed characters. Specific characters to definitely disallow (meaning don't put them on your whitelist) as long as they aren't needed by the user are <, >, ', and " If possible, enumerate all valid use cases and disallow everything else. If you have a very wide space of possible inputs, a blacklist may be used, but understand that you will forget something that makes you vulnerable.
Any characters that could potentially modify a SQL statement should be properly escaped.
Each time the data is accessed, both from the user's input and from your database, run it through the whitelist and escape filters recursively until the output matches the input (meaning all possible instances of the banned characters are eliminated. Consider what happens if you filter out <scipt> but don't apply it recursively to <sc<script>ript>.
Use parameterization whenever it is available. This one thing will solve a lot of your problems. A parameterization API is not always available however, so it's important to know about other techniques.

There are many APIs that provide this functionality, so try to find one before you roll your own. Beware of APIs that provide filtering/validation but aren't designed for security purposes. These will work 99.99% of the time, until you are targeted by a 1337.

PHP Soap - Remote SQL

2 Answers2