PHP Replace quotation marks surrounding a string

Question

I am trying to make my PDO query dynamic by running some logical operations before building my query.

However when the query is currently built no results are returned because the column name I am referencing in surrounded in quotations ( at least I believe this is the problem ), how would I go about removing quotation marks surrounding my string?

Logical Operation & Query

       // Logical comparison
       if($psiA >= $psiB){
            $highestPsi = "high_psi";
       } else {
            $highestPsi = "flow_psi";
       }

       if($gpmA >= $gpmB){
            $highestGpm = "high_gpm";
       } else {
            $highestGpm = "flow_gpm";
       }

       var_dump($highestGpm);
       var_dump($highestPsi);

       // Set up query variables
       if( $pVal <= 0 && $gVal <= 0) {
           $sql = "SELECT * FROM pumps WHERE pump_type = :pType AND :thePsi >= :pVal AND :theGpm >= :gVal AND pump_category = :cVal";
       } else {
           $sql = "SELECT * FROM pumps WHERE pump_type = :pType AND :thePsi >= :pVal AND :theGpm >= :gVal AND pump_category = :cVal";
           var_dump($sql);
       }

        // Build and execute query
       $stmt = $connection->prepare( $sql );

       $stmt->bindParam(':pType', $pType, PDO::PARAM_STR);
       $stmt->bindParam(':pVal', $pVal, PDO::PARAM_STR);
       $stmt->bindParam(':gVal', $gVal, PDO::PARAM_STR);
       $stmt->bindParam(':cVal', $cVal, PDO::PARAM_STR);
       $stmt->bindParam(':thePsi', $highestPsi, PDO::PARAM_STR);
       $stmt->bindParam(':theGpm', $highestGpm, PDO::PARAM_STR);

       $stmt->execute(array( 
                      'pType'   => $pType, 
                      'pVal'    => $pVal, 
                      'gVal'    => $gVal,
                      'cVal'    => $cVal,
                      'thePsi'  => $highestPsi,
                      'theGpm'  => $highestGpm
        ));

I know that the dump is returning the correct column names, I am certain that I need to perform some sort of regex to strip the quotes for this to work.

Any pointers would be most appreciated.

Well first you're binding values twice. Either bindParam or an array in execute. — Virus721
– Virus721, Commented Aug 28, 2013 at 14:31
are you binding a field name as a parameter? because that wont work, just concat it into your query string, as long as its not user input, its injection safe (unless you do something stupid) — x4rf41
– x4rf41, Commented Aug 28, 2013 at 14:31

Your Common Sense · Accepted Answer · 2013-08-28 14:54:50Z

1

PDO's prepared statement can represent a data literal only. Thus, a developer have to take care of identifiers oneself - PDO offers no help for the matter.

So, you have to either use any other library instead of PDO, such as safeMysql:

$sql = "SELECT * FROM pumps WHERE 
        pump_type = ?s AND ?n >= ?s AND ?n >= ?s AND pump_category = ?s";
$data = $db->getAll($sql, $pType, $highestPsi, $pVal, $highestGpm, $gVal, $cVal);

Or you have to format your identifiers manually. To format an identifier, one have to apply these 2 rules:

Enclose identifier in backticks.
Escape backticks inside by doubling them.

After such formatting, it is safe to insert the $table variable into query. So, the code would be:

$psi = "`".str_replace("`","``",$highestPsi)."`";
$sql = "SELECT * FROM pumps WHERE pump_type = :pType AND $psi ...";

edited Aug 28, 2013 at 14:54

answered Aug 28, 2013 at 14:40

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

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

3 Comments

Halfpint Over a year ago

Thankyou! I shall attempt to get this working with your feedback, much appreciated.

Halfpint Over a year ago

May I query why :pType is now surrounded in quotes? Is that the standard PDO format or is that optional?

Your Common Sense Over a year ago

just a copy-paste bug. there should be NO quotes at all

deceze · Accepted Answer · 2013-08-28 14:33:16Z

0

Nope, quotes are not really the problem. The problem is that you cannot bind any structural elements like column or table names. You can only bind values. You'll have to do with good old string concatenation/sprintf if you need to dynamically alter column names.

answered Aug 28, 2013 at 14:33

deceze♦

525k89 gold badges807 silver badges954 bronze badges

3 Comments

eyelidlessness Over a year ago

And do extensive validation!

deceze Over a year ago

...or at least whitelisting.

Halfpint Over a year ago

Ahh I see, thanks for your response! I'll try fathom a solution )

Collectives™ on Stack Overflow

PHP Replace quotation marks surrounding a string

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related