How do I filter value with prepared statements to keep security?

Question

I use Prepared Statements mostly to prevent SQL injections. Now I also need to filter a ENUM type. But how should I use it in my prepared statements to maintain security?

I have a table of addresses and need to filter on the user's invoice addresses. How do I do that and keep the security? Or does it not matter?

Two options I can think of. "Invoice" in the array as a string.

public function getCustomerInvoiceAddresses($customerNumber)
{
    $query = 'SELECT contactPerson, company, street, zipCode, city, deliveryMethod 
                FROM address 
                where FK_customerNumber = ? 
                AND addressType = ?';
    $paramType = 'is';
    $paramValue = array(
        $customerNumber,
        "Invoice"
    );
    $invoiceAddressArray = $this->ds->select($query, $paramType, $paramValue);
    return $invoiceAddressArray;
}

Invoice in the SELECT

public function getCustomerInvoiceAddresses($customerNumber)
{
    $query = 'SELECT contactPerson, company, street, zipCode, city, deliveryMethod 
                FROM address 
                where FK_customerNumber = ? 
                AND addressType = "Invoice"';
    $paramType = 'is';
    $paramValue = array(
        $customerNumber
    );
    $invoiceAddressArray = $this->ds->select($query, $paramType, $paramValue);
    return $invoiceAddressArray;
}

Or should I pass the string "Invoice" when I call the function?

$invoiceAddresses = $customer->getCustomerInvoiceAddresses($customerNumber, "Invoice");

it doesn't matter, use either, though the first one makes more sense and the last one makes least. — Your Common Sense, Feb 15 '21 at 16:54
you should learn some security. at least to the degree that security matters only for **variables** while *constant* parts of SQL are obviously being secure — Your Common Sense, Feb 15 '21 at 17:01

score -1 · Answer 1 · answered Feb 15 '21 at 16:58

-1

If you are parsing the data and using prepare statements I don't believe that SQL injection can happen. So I would use the most flexible choice and use

$invoiceAddresses = $customer->getCustomerInvoiceAddresses($customerNumber, "Invoice"); rename the function to getCustomerAddresses

answered Feb 15 '21 at 16:58

julien Lanoue

19
2

this is not flexible but rather overkill. the method is called getCustomer**Invoice**Addresses and repeating "Invoice" two times makes no sense – Your Common Sense Feb 15 '21 at 16:59
The whole function is overkill though, it could literally be a two-liner one for the query and the other for the function call $invoiceAddressArray = $this->ds->select($query, 'is', array( $customerNumber )); – julien Lanoue Feb 15 '21 at 17:02
the whole function is not – Your Common Sense Feb 15 '21 at 17:03

How do I filter value with prepared statements to keep security?

1 Answers1