Parameter Binding

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?

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.

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?

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

Select allOpen in new window

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

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?

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');

Select allOpen 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.

Could I also get a few injection examples in order to try against my forms?

By the  way, what are the MySQLi escape functions

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.

> 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.

If I reconsider my decision and use PDO with Parameter binding, will I need to worry about injections or other types of security breaches?

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.