Solved

Parameter Binding

Posted on 2015-01-14
16
75 Views
Last Modified: 2015-01-26
Hi Experts,

Why would you use parameters binding instead of incorporating the $variable, as I find incorporating a variable much more quicker? I have read that a variable can be injected, but if you write a function to check for key symbols and keywords (like semicolons, DROP, ALTER,...) and encapsulate them in a special way, like "\;", then wouldn't that be sufficient?

Does any one have such a function, or a list of keywords and key symbols?

Thank you,
0
Comment
Question by:APD_Toronto
  • 6
  • 5
  • 3
  • +1
16 Comments
 
LVL 11

Accepted Solution

by:
tel2 earned 167 total points
ID: 40550336
Hi APD,

> "Why would you use parameters binding instead of incorporating the $variable, as I find incorporating a variable much more quicker?"

Only quicker if you don't have to vet the $variable, I guess.

Binding the variable allows you to safely insert any characters into the field, including single and double quotes, and whatever SQL injection attempts the user may type.

I don't know of such a funtion, APD.  As you probably know, PHP's mysql-real-escape-string() function helps with some issues, but not the things you seem to be concerned about.

Binding variables takes some getting used to, but I don't know a better way.

But, I don't know much about PHP.
0
 
LVL 82

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 167 total points
ID: 40550367
Since PHP is a language with a lot of features, I'm going to start by posting the main manual page:  http://php.net/manual/en/   There is a link to Database Security http://php.net/manual/en/security.database.php and another to Database Extensions http://php.net/manual/en/refs.database.php  And on the page about PDO / MySQL is this statement:
PDO_MYSQL will take advantage of native prepared statement support present in MySQL 4.1 and higher.
This http://php.net/manual/en/mysqli.real-escape-string.php is the page about escaping characters to limit SQL injection problems.  That page shows the list of characters that are affected.

It is still a good idea to write your routine to check for other kinds of problems.  A MySQL query in PHP normally only allows a single query to be made so that helps to some degree.

All these features are kind of like using anti-virus on your computer.  Yes, it does slow things down but not doing them makes you more vulnerable to attacks.  On some of the web sites I maintain, I keep track of all of the form submissions.  People try to break in almost everyday.
0
 
LVL 11

Expert Comment

by:tel2
ID: 40550446
Well said, Dave.

> "A MySQL query in PHP normally only allows a single query to be made so that helps to some degree."

Yes, I've seen this in Perl, too.
Do you know of an an SQL injection example, which consists of a single SQL query?
0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 40550555
No but I wonder about that since SQL injection is supposed to be about that.  I should do a test to see if I can make it happen.  I am pretty certain that people spamming my forms aren't getting in because I can see the things they put in the forms.
0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 166 total points
ID: 40551000
This article may help answer some of your questions.
http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

The main advantage of parameter binding is that you're sending information to the DB engine, but you're not modifying a query string.  The query string is like a little computer program -- it tells the DB engine what to do.  When you start making changes to a PHP variable, especially if you're using external data, it's possible to get the query string wrong.

FWIW, this has never been a problem when I have 100% control over the ways that data get put into the queries, but if you're developing a large application that may have many programmers and many clients, your control may not be that great, and parameterized queries may give you (or your management) a greater sense of security.
0
 

Author Comment

by:APD_Toronto
ID: 40551831
Great article Ray,

To answer your question, I will be the only programmer who develop and maintain this software and I always check data input. So, would you use parameterized queries?
0
 

Author Comment

by:APD_Toronto
ID: 40551958
I am also leaning towards not using parameters because I want to use MySqli, which does not accept named parameters, where my queries can have up to 20 +parameters.

Can any one provide me with a list of key words and key statements that I need to check for in order to prevent injections?

For a simple older application I have the following in ASP, but I would change the replace to string just to escape the original string, would this be sufficient.

Function Injection(x)

'should upgrade to mixed cases, like If InSTR(UCase(x), "DROP"

    x = Replace(x,"select","")
    x = Replace(x,"drop","")
    x = Replace(x,"--","")
    x = Replace(x,"insert","")
    x = Replace(x,"delete","")
    x = Replace(x,"xp_","")
    x = Replace(x,"*","")
    x = Replace(x,"#","")
    x = Replace(x,"%","")
    x = Replace(x,"&","")
    x = Replace(x,"'","")
    x = Replace(x,"(","")
    x = Replace(x,")","")
    'x = Replace(x,"/","") <-- dates
    x = Replace(x,"\","")
    'x = Replace(x,":","")
    x = Replace(x,";","")
    x = Replace(x,"<","")
    x = Replace(x,">","")
    x = Replace(x,"=","")
    x = Replace(x,"[","")
    x = Replace(x,"]","")
    x = Replace(x,"?","")
    x = Replace(x,"`","")
    x = Replace(x,"|","")
    Injection = x
End Function

Open in new window

0
 
LVL 11

Expert Comment

by:tel2
ID: 40552392
I'll leave your latest question for others to answer, but could you please answer these for me, APD:

Q1. When you say MySQLi doesn't accept named parameters, are you concerned that this makes the code hard to write/read and it's too easy for the programmer to mix up the parameters, or what?

Q2. What if someone legitimately enters a string/word/name which contains any of those words in your banned list?  For example, "Mr Droppler" may be a valid name.  Would you be happy for your code to change that kind of thing to "Mr pler"?

tel2
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:APD_Toronto
ID: 40552443
Tel2,

A1. As I mentioned some of my queries will have 20 parameters, so having 20 ? marks to fill in and troubleshoot will be very tough.

A2. This is why I asked in my question that I would escape these. Now that I think about it, I think I would only need to escape symbols like semicolon, /*, */, and --. Would that not be sufficient?
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40552464
The right way to think about the question is with the security mantra: Accept Only Known Good Values.  When you plan that way, you build filters for every possible input.  Example: If you expect an American phone number, use a filter like this.  Same thing goes for all other inputs.  Looking for an integer?  Don't accept anything that does not pass the appropriate filter.  PHP has a lot of built-in filters.  See: http://php.net/manual/en/book.filter.php  For other, more application-specific, you can write your own filters like this.
<?php // demo/validate_phone_numbers.php
error_reporting(E_ALL);

// A FUNCTION TO VALIDATE A USA PHONE NUMBER AND RETURN A NORMALIZED STRING
// MAN PAGE: http://discuss.fogcreek.com/joelonsoftware3/default.asp?cmd=show&ixPost=102667&ixReplies=15
// MAN PAGE: http://www.nanpa.com/number_resource_info/index.html

function strtophone($phone, $format=FALSE, $letters=FALSE, $dlm='-')
{
    if ($letters)
    {
        // TURN INPUT LIKE 1-800-BIG-DOGS
        // INTO INPUT LIKE 1-800-244-3647
        $phone = strtoupper($phone);
        if (preg_match('/[A-Z]/', $phone))
        {
            $phone = strtr
            ( $phone
            , 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
            , '22233344455566677778889999'
            )
            ;
        }
    }

    // DISCARD NON-NUMERIC CHARACTERS
    $phone = preg_replace('/[^0-9]/', NULL, $phone);

    // DISCARD A LEADING '1' FROM NUMBERS ENTERED LIKE 1-800-555-1212
    if (substr($phone,0,1) == '1') $phone = substr($phone,1);

    // IF LESS THAN TEN DIGITS, IT IS INVALID
    if (strlen($phone) < 10) return FALSE;

    // IF IT STARTS WITH '0' OR '1' IT IS INVALID, SECOND DIGIT CANNOT BE '9' (YET)
    if (substr($phone,0,1) == '0') return FALSE;
    if (substr($phone,0,1) == '1') return FALSE;
    if (substr($phone,1,1) == '9') return FALSE;

    // ISOLATE THE COMPONENTS OF THE PHONE NUMBER
    $ac = substr($phone,0,3); // AREA
    $ex = substr($phone,3,3); // EXCHANGE
    $nm = substr($phone,6,4); // NUMBER
    $xt = substr($phone,10);  // EXTENSION

    // ADD OTHER TESTS HERE AS MAY BE NEEDED - THESE ARE FOR LOCAL APPS
    if ($ac == '900') return FALSE;
    if ($ac == '976') return FALSE;
    if ($ex == '555') return FALSE;

    // IF NOT FORMATTED
    if (!$format) return $phone;

    // STANDARDIZE THE PRINTABLE FORMAT OF THE PHONE NUMBER LIKE 212-555-1212-1234
    $formatted_phone = $ac . $dlm . $ex . $dlm . $nm;
    if ($xt != '') $formatted_phone .= $dlm . $xt;
    return $formatted_phone;
}



// DEMONSTRATION OF THE FUNCTION IN ACTION.
if (!empty($_GET["p"]))
{
    // VALIDATE PHONE USING FUNCTION ABOVE
    if (!$phone = strtophone($_GET["p"], TRUE))
    {
        // FUNCTION RETURNS FALSE IF PHONE NUMBER IS UNUSABLE
        echo "BOGUS: {$_GET["p"]} ";
    }
    else
    {
        // SHOW THE FORMATTED PHONE
        echo "VALID: {$_GET["p"]} == $phone";
    }
}


// PUT UP A FORM TO TEST PHONE NUMBERS
function ph($p)
{
    echo "<br/><a href=\"{$_SERVER['PHP_SELF']}?p=" . urlencode($p) . "\">$p</a>" . PHP_EOL;
}

$form = <<<EOD
<form>
ENTER A PHONE NUMBER:
<input name="p" /><br/>
<input type="submit" />
</form>
TRY SOME OF THESE (CLICK OR COPY AND PASTE):
EOD;

echo $form;

ph('1-800-5551212');
ph('202-537-7560');
ph('202 537 7560');
ph('1-202-537-7560');
ph('(202) 537-7560');
ph('1.202.537.7560');
ph('123456789');
ph('703-356-5300 x2048');
ph('(212) 555-1212');
ph('1 + (212) 555-1212');
ph('1 (292) 226-7000');

Open in new window

The point here is to make sure that you're working with Good data, not trying to exclude Bad data.  If you do that and you use the MySQLi escape functions, you will be all right.
0
 

Author Comment

by:APD_Toronto
ID: 40554298
Could I also get a few injection examples in order to try against my forms?
0
 

Author Comment

by:APD_Toronto
ID: 40554319
By the  way, what are the MySQLi escape functions
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40554630
Your best example is here:
http://php.net/manual/en/mysqli.real-escape-string.php

You need to call this once for every variable you put into the SQL query string.  Could be lots of calls, but don't be worried about performance.  It's fast and confers safety.
0
 
LVL 11

Expert Comment

by:tel2
ID: 40554740
> Your best example is here:
> http://php.net/manual/en/mysqli.real-escape-string.php


Which is the same link Dave supplied in post #40550367, above.
0
 

Author Comment

by:APD_Toronto
ID: 40563203
If I reconsider my decision and use PDO with Parameter binding, will I need to worry about injections or other types of security breaches?
0
 
LVL 11

Expert Comment

by:tel2
ID: 40571712
Hi APD,

Thanks for the point.  I guess you didn't really get the answer you wanted, and I'm no expert in this subject (just pretending to be).

In response to your last post, I don't know if you will still be subject to injections, but I don't think parameter binding will protect you from other types of security breaches.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now