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.'.$mysqli->error.'
'.$query.'
'
));
}
//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.'.$mysqli->error.'
'.$query.'
'
));
}
//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.'.$mysqli->error.'
'.$query.'
'
));
}
// 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.'.$mysqli->error.'
'.$query.'
'
));
}
//close database connection
$mysqli->close();
}
Answer
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),
No comments:
Post a Comment