-1

I was advised I needed to update my functions slightly so it's more secure against SQL Injections and that is to use mysqli prepare, bind_param and execute which I have successfully done on 1 function that creates a customer to a single table in the database.

I have a lot of functions but I can go through and do all those once I know how on these few: update table query, delete table query and my main one which currently as you will see from code it does a multi_query to store data in 3 different tables.

Query I have done and got working:

// Create customer
if ($action == 'create_customer'){

    // invoice customer information
    // billing
    $customer_name = $_POST['customer_name']; // customer name
    $customer_email = $_POST['customer_email']; // customer email
    $customer_address_1 = $_POST['customer_address_1']; // customer address
    $customer_address_2 = $_POST['customer_address_2']; // customer address
    $customer_town = $_POST['customer_town']; // customer town
    $customer_county = $_POST['customer_county']; // customer county
    $customer_postcode = $_POST['customer_postcode']; // customer postcode
    $customer_phone = $_POST['customer_phone']; // customer phone number

    //shipping
    $customer_name_ship = $_POST['customer_name_ship']; // customer name (shipping)
    $customer_address_1_ship = $_POST['customer_address_1_ship']; // customer address (shipping)
    $customer_address_2_ship = $_POST['customer_address_2_ship']; // customer address (shipping)
    $customer_town_ship = $_POST['customer_town_ship']; // customer town (shipping)
    $customer_county_ship = $_POST['customer_county_ship']; // customer county (shipping)
    $customer_postcode_ship = $_POST['customer_postcode_ship']; // customer postcode (shipping)

    $query = "INSERT INTO store_customers (
                    name,
                    email,
                    address_1,
                    address_2,
                    town,
                    county,
                    postcode,
                    phone,
                    name_ship,
                    address_1_ship,
                    address_2_ship,
                    town_ship,
                    county_ship,
                    postcode_ship
                ) VALUES (
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?
                );
            ";

    /* Prepare statement */
    $stmt = $mysqli->prepare($query);
    if($stmt === false) {
      trigger_error('Wrong SQL: ' . $query . ' Error: ' . $mysqli->error, E_USER_ERROR);
    }

    /* Bind parameters. TYpes: s = string, i = integer, d = double,  b = blob */
    $stmt->bind_param(
        'ssssssssssssss',
        $customer_name,$customer_email,$customer_address_1,$customer_address_2,$customer_town,$customer_county,$customer_postcode,
        $customer_phone,$customer_name_ship,$customer_address_1_ship,$customer_address_2_ship,$customer_town_ship,$customer_county_ship,$customer_postcode_ship);

    if($stmt->execute()){
        //if saving success
        echo json_encode(array(
            'status' => 'Success',
            'message' => 'Customer has been created successfully!'
        ));
    } else {
        // if unable to create invoice
        echo json_encode(array(
            'status' => 'Error',
            'message' => 'There has been an error, please try again.'
            // debug
            //'message' => 'There has been an error, please try again.<pre>'.$mysqli->error.'</pre><pre>'.$query.'</pre>'
        ));
    }

    //close database connection
    $mysqli->close();
}

Examples I need help with:

// Create invoice
if ($action == 'create_invoice'){

    // invoice customer information
    // billing
    $customer_name = $_POST['customer_name']; // customer name
    $customer_email = $_POST['customer_email']; // customer email
    $customer_address_1 = $_POST['customer_address_1']; // customer address
    $customer_address_2 = $_POST['customer_address_2']; // customer address
    $customer_town = $_POST['customer_town']; // customer town
    $customer_county = $_POST['customer_county']; // customer county
    $customer_postcode = $_POST['customer_postcode']; // customer postcode
    $customer_phone = $_POST['customer_phone']; // customer phone number

    //shipping
    $customer_name_ship = $_POST['customer_name_ship']; // customer name (shipping)
    $customer_address_1_ship = $_POST['customer_address_1_ship']; // customer address (shipping)
    $customer_address_2_ship = $_POST['customer_address_2_ship']; // customer address (shipping)
    $customer_town_ship = $_POST['customer_town_ship']; // customer town (shipping)
    $customer_county_ship = $_POST['customer_county_ship']; // customer county (shipping)
    $customer_postcode_ship = $_POST['customer_postcode_ship']; // customer postcode (shipping)

    // invoice details
    $invoice_number = $_POST['invoice_id']; // invoice number
    $invoice_date = $_POST['invoice_date']; // invoice date
    $invoice_due_date = $_POST['invoice_due_date']; // invoice due date
    $invoice_subtotal = $_POST['invoice_subtotal']; // invoice sub-total
    $invoice_shipping = $_POST['invoice_shipping']; // invoice shipping amount
    $invoice_discount = $_POST['invoice_discount']; // invoice discount
    $invoice_vat = $_POST['invoice_vat']; // invoice vat
    $invoice_total = $_POST['invoice_total']; // invoice total
    $invoice_notes = $_POST['invoice_notes']; // Invoice notes
    $invoice_type = $_POST['invoice_type']; // Invoice type
    $invoice_status = $_POST['invoice_status']; // Invoice status

    // insert invoice into database
    $query = "INSERT INTO invoices (
                    invoice, 
                    invoice_date, 
                    invoice_due_date, 
                    subtotal, 
                    shipping, 
                    discount, 
                    vat, 
                    total,
                    notes,
                    invoice_type,
                    status
                ) VALUES (
                    '".$invoice_number."',
                    '".$invoice_date."',
                    '".$invoice_due_date."',
                    '".$invoice_subtotal."',
                    '".$invoice_shipping."',
                    '".$invoice_discount."',
                    '".$invoice_vat."',
                    '".$invoice_total."',
                    '".$invoice_notes."',
                    '".$invoice_type."',
                    '".$invoice_status."'
                );
            ";
    // insert customer details into database
    $query .= "INSERT INTO customers (
                    invoice,
                    name,
                    email,
                    address_1,
                    address_2,
                    town,
                    county,
                    postcode,
                    phone,
                    name_ship,
                    address_1_ship,
                    address_2_ship,
                    town_ship,
                    county_ship,
                    postcode_ship
                ) VALUES (
                    '".$invoice_number."',
                    '".$customer_name."',
                    '".$customer_email."',
                    '".$customer_address_1."',
                    '".$customer_address_2."',
                    '".$customer_town."',
                    '".$customer_county."',
                    '".$customer_postcode."',
                    '".$customer_phone."',
                    '".$customer_name_ship."',
                    '".$customer_address_1_ship."',
                    '".$customer_address_2_ship."',
                    '".$customer_town_ship."',
                    '".$customer_county_ship."',
                    '".$customer_postcode_ship."'
                );
            ";

    // invoice product items
    foreach($_POST['invoice_product'] as $key => $value) {
        $item_product = $value;
        // $item_description = $_POST['invoice_product_desc'][$key];
        $item_qty = $_POST['invoice_product_qty'][$key];
        $item_price = $_POST['invoice_product_price'][$key];
        $item_discount = $_POST['invoice_product_discount'][$key];
        $item_subtotal = $_POST['invoice_product_sub'][$key];

        // insert invoice items into database
        $query .= "INSERT INTO invoice_items (
                invoice,
                product,
                qty,
                price,
                discount,
                subtotal
            ) VALUES (
                '".$invoice_number."',
                '".$item_product."',
                '".$item_qty."',
                '".$item_price."',
                '".$item_discount."',
                '".$item_subtotal."'
            );
        ";

    }

    header('Content-Type: application/json');

    // execute the query
    if($mysqli -> multi_query($query)){
        //if saving success
        echo json_encode(array(
            'status' => 'Success',
            'message' => 'Invoice has been created successfully!'
        ));

        //Set default date timezone
        date_default_timezone_set(TIMEZONE);
        //Include Invoicr class
        include('invoice.php');
        //Create a new instance
        $invoice = new invoicr("A4",CURRENCY,"en");
        //Set number formatting
        $invoice->setNumberFormat('.',',');
        //Set your logo
        $invoice->setLogo(COMPANY_LOGO,COMPANY_LOGO_WIDTH,COMPANY_LOGO_HEIGHT);
        //Set theme color
        $invoice->setColor(INVOICE_THEME);
        //Set type
        $invoice->setType($invoice_type);
        //Set reference
        $invoice->setReference($invoice_number);
        //Set date
        $invoice->setDate($invoice_date);
        //Set due date
        $invoice->setDue($invoice_due_date);
        //Set from
        $invoice->setFrom(array(COMPANY_NAME,COMPANY_ADDRESS_1,COMPANY_ADDRESS_2,COMPANY_COUNTY,COMPANY_POSTCODE,COMPANY_NUMBER,COMPANY_VAT));
        //Set to
        $invoice->setTo(array($customer_name,$customer_address_1,$customer_address_2,$customer_town,$customer_county,$customer_postcode,"Phone: ".$customer_phone));
        //Ship to
        $invoice->shipTo(array($customer_name_ship,$customer_address_1_ship,$customer_address_2_ship,$customer_town_ship,$customer_county_ship,$customer_postcode_ship,''));
        //Add items
        // invoice product items
        foreach($_POST['invoice_product'] as $key => $value) {
            $item_product = $value;
            // $item_description = $_POST['invoice_product_desc'][$key];
            $item_qty = $_POST['invoice_product_qty'][$key];
            $item_price = $_POST['invoice_product_price'][$key];
            $item_discount = $_POST['invoice_product_discount'][$key];
            $item_subtotal = $_POST['invoice_product_sub'][$key];

            if(ENABLE_VAT == true) {
                $item_vat = (VAT_RATE / 100) * $item_subtotal;
            }

            $invoice->addItem($item_product,'',$item_qty,$item_vat,$item_price,$item_discount,$item_subtotal);
        }
        //Add totals
        $invoice->addTotal("Total",$invoice_subtotal);
        if(!empty($invoice_discount)) {
            $invoice->addTotal("Discount",$invoice_discount);
        }
        if(!empty($invoice_shipping)) {
            $invoice->addTotal("Delivery",$invoice_shipping);
        }
        if(ENABLE_VAT == true) {
            $invoice->addTotal("TAX/VAT ".VAT_RATE."%",$invoice_vat);
        }
        $invoice->addTotal("Total Due",$invoice_total,true);
        //Add Badge
        $invoice->addBadge($invoice_status);
        // Customer notes:
        if(!empty($invoice_notes)) {
            $invoice->addTitle("Cusatomer Notes");
            $invoice->addParagraph($invoice_notes);
        }
        //Add Title
        $invoice->addTitle("Payment information");
        //Add Paragraph
        $invoice->addParagraph(PAYMENT_DETAILS);
        //Set footer note
        $invoice->setFooternote(FOOTER_NOTE);
        //Render the PDF
        $invoice->render('invoices/'.$invoice_number.'.pdf','F');
    } else {
        // if unable to create invoice
        echo json_encode(array(
            'status' => 'Error',
            'message' => 'There has been an error, please try again.'
            // debug
            //'message' => 'There has been an error, please try again.<pre>'.$mysqli->error.'</pre><pre>'.$query.'</pre>'
        ));
    }

    //close database connection
    $mysqli->close();

}

// Adding new product
if($action == 'delete_invoice') {

    // Connect to the database
    $mysqli = new mysqli(DATABASE_HOST, DATABASE_USER, DATABASE_PASS, DATABASE_NAME);

    // output any connection error
    if ($mysqli->connect_error) {
        die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error);
    }

    $id = $_POST["delete"];

    // the query
    $query = "DELETE FROM invoices WHERE invoice = ".$id.";";
    $query .= "DELETE FROM customers WHERE invoice = ".$id.";";
    $query .= "DELETE FROM invoice_items WHERE invoice = ".$id.";";

    unlink('invoices/'.$id.'.pdf');

    if($mysqli -> multi_query($query)) {
        //if saving success
        echo json_encode(array(
            'status' => 'Success',
            'message'=> 'Product has been deleted successfully!'
        ));

    } else {
        //if unable to create new record
        echo json_encode(array(
            'status' => 'Error',
            //'message'=> 'There has been an error, please try again.'
            'message' => 'There has been an error, please try again.<pre>'.$mysqli->error.'</pre><pre>'.$query.'</pre>'
        ));
    }

    // close connection 
    $mysqli->close();

}

// Adding new product
if($action == 'update_customer') {

    // Connect to the database
    $mysqli = new mysqli(DATABASE_HOST, DATABASE_USER, DATABASE_PASS, DATABASE_NAME);

    // output any connection error
    if ($mysqli->connect_error) {
        die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error);
    }

    $getID = $_POST['id']; // id

    // invoice customer information
    // billing
    $customer_name = $_POST['customer_name']; // customer name
    $customer_email = $_POST['customer_email']; // customer email
    $customer_address_1 = $_POST['customer_address_1']; // customer address
    $customer_address_2 = $_POST['customer_address_2']; // customer address
    $customer_town = $_POST['customer_town']; // customer town
    $customer_county = $_POST['customer_county']; // customer county
    $customer_postcode = $_POST['customer_postcode']; // customer postcode
    $customer_phone = $_POST['customer_phone']; // customer phone number

    //shipping
    $customer_name_ship = $_POST['customer_name_ship']; // customer name (shipping)
    $customer_address_1_ship = $_POST['customer_address_1_ship']; // customer address (shipping)
    $customer_address_2_ship = $_POST['customer_address_2_ship']; // customer address (shipping)
    $customer_town_ship = $_POST['customer_town_ship']; // customer town (shipping)
    $customer_county_ship = $_POST['customer_county_ship']; // customer county (shipping)
    $customer_postcode_ship = $_POST['customer_postcode_ship']; // customer postcode (shipping)

    // the query
    $query = "UPDATE store_customers SET
                name = '".$customer_name."',
                email = '".$customer_email."',
                address_1 = '".$customer_address_1."',
                address_2 = '".$customer_address_2."',
                town = '".$customer_town."',
                county = '".$customer_county."',
                postcode = '".$customer_postcode."',
                phone = '".$customer_phone."',

                name_ship = '".$customer_name_ship."',
                address_1_ship = '".$customer_address_1_ship."',
                address_2_ship = '".$customer_address_2_ship."',
                town_ship = '".$customer_town_ship."',
                county_ship = '".$customer_county_ship."',
                postcode_ship = '".$customer_postcode_ship."'

                WHERE invoice = '".$getID. "'

            ";

    //execute the query
    if($mysqli -> query($query)) {
        //if saving success
        echo json_encode(array(
            'status' => 'Success',
            'message'=> 'Customer has been updated successfully!'
        ));

    } else {
        //if unable to create new record
        echo json_encode(array(
            'status' => 'Error',
            //'message'=> 'There has been an error, please try again.'
            'message' => 'There has been an error, please try again.<pre>'.$mysqli->error.'</pre><pre>'.$query.'</pre>'
        ));
    }

    //close database connection
    $mysqli->close();

}
halfer
  • 19,471
  • 17
  • 87
  • 173
James
  • 1,600
  • 18
  • 46
  • And what is the problem? You can't print `?` and bind values or what? – u_mulder May 23 '15 at 20:14
  • You just need to apply on the lower script the same thing you did on the upper one. In other words, replace all variables with `?` and then define them in `bind_param()`. Just identify which variables are strings and which are integers, and define them as such. – nomistic May 23 '15 at 20:16
  • yea ive worked out UPDATE and DELETE, ive read those cannot prepare / bind and execute on multiple queries so in my example the create_invoice part so do i just leave it like it is? if so how can i secure that for SQL Injections as im updating these to use prepare / bind and execute to help with possible injections you see. – James May 23 '15 at 20:22

1 Answers1

0

select, insert, and update all work the same way.

for example, instead of inserting $variable1 you would do it this way (assuming, for example, that var1 is an integer and var2 is a string)

$stmt = $conn->prepare("insert into tablename (key1, key2) values (?, ?)");
$stmt->bind_param("is", $value1,$value2);
$stmt->execute();
$stmt->close();

Same thing for an update (this time assuming the first two are strings and the last one is an integer):

$stmt = $conn->prepare("update tablename set var1= ? var2 = ? where var3 = ?");
$stmt->bind_param("ssi", $var1, $var2, $var3);
$stmt->execute();
$stmt->close();

It's the same basic principle.

Edit

To be able to handle your substrings, you can just create different bind_param() rows in your conditional statements. Just do it all before running execute()

Another Edit

Unfortunately you cannot use multi_query with bind_param however, you can handle this much easier with insert_id and use multiple queries. It won't hurt performance and will make debugging much easier.

Here's an example:

$query1 = "INSERT INTO invoices (
                invoice, 
                invoice_date
            ) VALUES (?, ?);
        ";


$stmt = $mysqli->prepare($query1);
$stmt->bind_param("ss", $invoice, $invoice_date);
$stmt->execute();
$stmt->close();

$invoice = $mysqli->insert_id;

// insert customer details into database


$query2 = "INSERT INTO customers (
                invoice,
                name

            ) VALUES (?,?)"
$stmt = $mysqli->prepare($query2);
$stmt->bind_param("ss", $invoice, $name);
$stmt->execute();
$stmt->close();

and then run the other queries separately (it also would be easier to set up your bind_param() if done separately like this),

nomistic
  • 2,857
  • 4
  • 18
  • 36
  • yep ive managed to work that out but thanks for your answer as ill mark as valid however i see you cannot prepare / bind / execute for multiple queries but can only use multi_query so that function is valid but i would like to secure that one but as the code for that does not need to change do you know how i can secure it from possible sql injections? – James May 23 '15 at 20:29
  • you don't need to run multiple queries, but binding them separately *should* work, as you are just binding them to the object. You can also do this: http://stackoverflow.com/a/11635679/3044080 – nomistic May 23 '15 at 20:34
  • as long as you bind your variables into an object and submit them as prepared, you should be reasonably safe from sql injections – nomistic May 23 '15 at 20:36
  • I am new to this so learning, on the create_invoice action and the stuff / multi_query i have within that would you mind re-writing how it should be? i know an ask but im confused and would really help me out plus i can see how it should be done and update the others that have multi_query. Ive just done all the single ones for update, delete, insert in single ones :) thanks in advance! really appreciate it! – James May 23 '15 at 20:41
  • 1
    I won't write your code for you, but I showed an abbreviated form. See edits. (note you are making it much more difficult than it needs to be. Each query can be run separately, and tie them together with insert_id. I showed that after). – nomistic May 23 '15 at 21:03
  • oh right, from your example seems simple with i can just use 1 bind_param and continue into the queries... i understand. ill give it a crack :) thnks for your help! – James May 23 '15 at 21:29
  • actually, do it the second way (break it apart into separate queries and use `insert_id`) I just realized that you can't use `multi_query` with `bind_param`. It may err with the execution. I am going to correct the answer – nomistic May 23 '15 at 21:31
  • I was just about to ask about if($mysqli -> multi_query($query)) { as i read you cannot do it :) ill check your update. – James May 23 '15 at 21:31
  • it's changed. It's a different method than you were using, but FAR easier to see what is going on, and cleaner. – nomistic May 23 '15 at 21:38
  • yea i understand it and will work well, just quickly... in regards to how i handle the json responses..... do you know how i can still do those if i use this new method? – James May 23 '15 at 21:39
  • would using if($stmt->execute()){ work for them all? – James May 23 '15 at 21:40
  • that should not affect your queries, which are all php variables; it doesn't matter where the variables are coming from. and yes to your last question, because you are closing it each time with `$stmt->close();` so the object gets reassigned. – nomistic May 23 '15 at 21:42