I have a perplexing issue that I can't seem to comprehend...
I have two SQL statements:
- The first enters information from a form into the database.
- The second takes data from the database entered above, sends an email, and then logs the details of the transaction
The problem is that it appears that a single quote is triggering a MySQL error on the second entry only! The first instance works without issue, but the second instance
triggers the mysql_error[].
Does the data from a form get handled differently from the data captured in a form?
Query 1 - This works without issue [and without escaping the single quote]
$result = mysql_query["INSERT INTO job_log
[order_id, supplier_id, category_id, service_id, qty_ordered, customer_id, user_id, salesperson_ref, booking_ref, booking_name, address, suburb, postcode, state_id, region_id, email, phone, phone2, mobile, delivery_date, stock_taken, special_instructions, cost_price, cost_price_gst, sell_price, sell_price_gst, ext_sell_price, retail_customer, created, modified, log_status_id]
VALUES
['$order_id', '$supplier_id', '$category_id', '{$value['id']}', '{$value['qty']}', '$customer_id', '$user_id', '$salesperson_ref', '$booking_ref', '$booking_name', '$address', '$suburb', '$postcode', '$state_id', '$region_id', '$email', '$phone', '$phone2', '$mobile', STR_TO_DATE['$delivery_date', '%d/%m/%Y'], '$stock_taken', '$special_instructions', '$cost_price', '$cost_price_gst', '$sell_price', '$sell_price_gst', '$ext_sell_price', '$retail_customer', '".date['Y-m-d H:i:s', time[]]."', '".date['Y-m-d H:i:s', time[]]."', '1']"];
Query 2 - This fails when entering a name with a single quote [for example, O'Brien]
$query = mysql_query["INSERT INTO message_log
[order_id, timestamp, message_type, email_from, supplier_id, primary_contact, secondary_contact, subject, message_content, status]
VALUES
['$order_id', '".date['Y-m-d H:i:s', time[]]."', '$email', '$from', '$row->supplier_id', '$row->primary_email' ,'$row->secondary_email', '$subject', '$message_content', '1']"];
As a PHP/MySQL developer, you have certain practices worth noting and making use of every other time. One of the is how to escape single quote in PHP while working with MySQL database. In this article, I will illustrate how to escape Single Quote in PHP/MySQL
Escaping refers to the process of encoding data containing characters so that MySQL interprets it correctly. To do this, you MUST escape strings with a PHP function known as mysql_real_escape_string. This means that you have to run this function in PHP before passing your query to the database. Normal good practice is to
escape any data that comes into your database from an eternal source so as to avoid potential SQL injection.
You have to escape your data before you build your query. Also, you can build your query programmatically using PHP’s looping constructs and range:
Example 1: Using Object Oriented style
While using Object Oriented method, you escape characters in strings as shown below: