PHP SQL concatenation

Question

I have a SQL query in which I want to select records that where paid between two dates.

Here's how I build my query:

$qry = array();
    $qry[] = "SELECT DISTINCT 2 AS Record_Type, wp_woocommerce_order_items.order_id As Order_Id, First_Name;
    $qry[] = "FROM  wp_woocommerce_order_items";
    $qry[] = "LEFT JOIN (SELECT meta_value As First_Name, post_id FROM wp_postmeta WHERE meta_key = '_shipping_first_name') AS a";
    $qry[] = "ON wp_woocommerce_order_items.order_id = a.post_id";
    $qry[] = "RIGHT JOIN (SELECT post_id FROM wp_postmeta WHERE meta_key = '_paid_date' AND meta_value > " .$_POST['debut'] . " AND meta_value < " . $_POST['fin'] . ") AS m";
    $qry[] = "ON wp_woocommerce_order_items.order_id = m.post_id";
    $qry[] = "WHERE wp_woocommerce_order_items.order_item_type = 'line_item'";
    $qry[] = "ORDER BY wp_woocommerce_order_items.order_id";

The problem is with my POST variables in the RIGHT JOIN line. If I hardcode a date instead of $_POST['debut'] and $_POST['fin'] I get the result I'm looking for.

So I guess my problem is with concatenation from my PHP POST variables.

Anyone can help me?

You are seriously exposing your back-end to significant risks of being compromised by an attacker if you embed user variables directly into your SQL query. — Halim Qarroum
– Halim Qarroum, Commented May 28, 2013 at 14:44
Your missing a double quote from the end of first element in the array — Phil Cross
– Phil Cross, Commented May 28, 2013 at 14:45

Patrice Levesque · Accepted Answer · 2013-05-28 14:45:07Z

3

You need to quote your values, e.g. instead of

$qry[] = "RIGHT JOIN (SELECT post_id FROM wp_postmeta WHERE meta_key = '_paid_date' AND meta_value > " .$_POST['debut'] . " AND meta_value < " . $_POST['fin'] . ") AS m";

use

$qry[] = "RIGHT JOIN (SELECT post_id FROM wp_postmeta WHERE meta_key = '_paid_date' AND meta_value > '" .$_POST['debut'] . "' AND meta_value < '" . $_POST['fin'] . "') AS m";

And please look for mysql_real_escape_string or other ways to avoid SQL injection attacks.

answered May 28, 2013 at 14:45

Patrice Levesque

2,11418 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

CharleyXIV Over a year ago

Allright thanks! I'm going take a look for SQL injection too.

Yogus · Accepted Answer · 2013-05-28 14:51:05Z

1

Use this

$qry[] = "SELECT DISTINCT 2 AS Record_Type, wp_woocommerce_order_items.order_id As Order_Id, First_Name
    FROM  wp_woocommerce_order_items
    LEFT JOIN (SELECT meta_value As First_Name, post_id FROM wp_postmeta WHERE meta_key = '_shipping_first_name') AS a
    ON wp_woocommerce_order_items.order_id = a.post_id
    RIGHT JOIN (SELECT post_id FROM wp_postmeta WHERE meta_key = '_paid_date' AND meta_value > '" .$_POST['debut'] . "' AND meta_value < '" . $_POST['fin'] . "'') AS m
    ON wp_woocommerce_order_items.order_id = m.post_id
    WHERE wp_woocommerce_order_items.order_item_type = 'line_item'
    ORDER BY wp_woocommerce_order_items.order_id";

answered May 28, 2013 at 14:51

Yogus

2,2705 gold badges23 silver badges40 bronze badges

Collectives™ on Stack Overflow

PHP SQL concatenation

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related