Php PDO sql statement with single quotes

Question

I have a mysql php pdo statement that has single quotes to call the Mysql Geolocation "POINT" function as such.

    $sql = "INSERT INTO userTrip 
        (userId, fromLat, fromLon, fromLoc, fromPOI,
        toLat, toLon, toLoc, toPOI,
        tripFinished, isMatched, departureTime, createdAt)
        values 
        (:user,:fromLat,:fromLon, GeomFromText('POINT(:fromLat1 :fromLon1)'),:fromPOI,:toLat,
        :toLon, GeomFromText('POINT(:toLat1 :toLon1)'),:toPOI,0,0,:departureTime,:date)";

    $stmt = $db->prepare($sql);        

    $stmt->bindParam(':user', $userId, PDO::PARAM_INT);
    $stmt->bindParam(':fromLat', $fromLat, PDO::PARAM_STR);
    $stmt->bindParam(':fromLon', $fromLon, PDO::PARAM_STR);
    $stmt->bindParam(":fromLat1", $fromLat, PDO::PARAM_STR);
    $stmt->bindParam(':fromLon1', $fromLon, PDO::PARAM_STR);
    $stmt->bindParam(':fromPOI', $fromPOI, PDO::PARAM_STR);
    $stmt->bindParam(':toLat', $toLat, PDO::PARAM_STR);
    $stmt->bindParam(':toLon', $toLon, PDO::PARAM_STR);        
    $stmt->bindParam(':toLat1', $toLat, PDO::PARAM_STR);        
    $stmt->bindParam(':toLon1', $toLon, PDO::PARAM_STR); 
    $stmt->bindParam(':toPOI', $toPOI, PDO::PARAM_STR);
    $stmt->bindParam(':departureTime', $departureTime, PDO::PARAM_STR);
    $stmt->bindParam(':date', date('Y-m-d H:i:s'), PDO::PARAM_STR);

when I execute the query it throws an exception "PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens" even though the number of parameters are correct. I suspect the single quotes throw off the query but I need to put them in. I tried escaping them using backslash and everything else I could think of but the query wont execute. Is there a way around this?

score 3 · Accepted Answer · answered Mar 31 '13 at 19:11

3

You don't need quotes. You need to pass a parameter into function. That's what prepared statements are for.

define a value first

$point = "POINT($fromLat $fromLon)";

then prepare your query usual way

..., GeomFromText(:point), ...

then bind this $point variable to :point placeholder.

answered Mar 31 '13 at 19:11

Your Common Sense

156,878
40
214
345

Yup thanks! I actually came across this post which is essentially the same thing you are suggesting above.. Thanks! http://stackoverflow.com/questions/10254558/php-pdo-prepared-statements-and-value-binding-gives-invalid-parameter-number-err?rq=1 – chintan Apr 01 '13 at 11:33

score -1 · Answer 2 · answered Mar 31 '13 at 18:52

If you need to embed a single quote in the SQL string, this

(:user,:fromLat,:fromLon, GeomFromText('POINT(:fromLat1 :fromLon1)'),:fromPOI,:toLat,

might need to be this.

(:user,:fromLat,:fromLon, GeomFromText(''POINT(:fromLat1 :fromLon1)''),:fromPOI,:toLat,
-- Two single quotes                   ^^                          ^^

Php PDO sql statement with single quotes

2 Answers2

Linked