PHP pass variables to my own MySQL function--DB using variable as column name

Question

This is sort of a follow up to my last question (http://stackoverflow.com/questions/12945119/mysql-function-add-prices-from-multiple-tables ) so the MySQL code is there. My background with DBs is all SQL Server 2008 and Access, so the idiosyncrasies of MySQL are getting the better of me. I've Googled my brains out, read every even vaguely relevant Q&A on StackOverflow, StackExchange's programming and webdeveloper boards, etc. (Had to get that disclaimer out of the way.)

I ran the function in DreamCoder and when it just prompts me for intput and then runs the function, it returns the correct answer (a decimal,) so the function's logic is sound as far as I can tell. However, when I try to call the function from PHP, it errors out.

The DB access code is as follows:

function getQuote($json)

echo("JSON input: ");
var_dump($json);

    $array = json_decode($json, TRUE);
    echo("JSON decoded array: ");
    print_r($array);

    $cheese = $array[0];
    $meat = $array[1];
    $veg = $array[2];   

    $c = db_connect();
    $query = "SELECT calculatePrice($cheese, $meat, $veg)";
    var_dump($query);
    $result  = mysql_query($query , $c);
    if(!$result)
    {
        echo "\nCould not successfully run query ($query) from DB: " . mysql_error();
        exit;
    }

if (mysql_num_rows($result) == 0) {
    echo "No rows found, nothing to print so am exiting";
    exit;
}

while($row = mysql_fetch_assoc($result)){
    echo("Row: ");
    var_dump($row);

    if(!$row)
        die("No price returned!" . var_dump($row));
    else
    {   
        return json_encode($row);   
    }
}  //end while

And I wrote the following "test harness" to manually feed it data. I know the JSON's going in right because the full code gives the same error verbatim, so the issues's either PHP or MySQL.

    <?php
        require_once 'DBUtils.php';

    $array = array();
    $array[] = "Chedder";
    $array[] ="Ham";
    $array[] ="Carrot";

    $json = json_encode($array);
    echo("JSON object:");
    var_dump($json);

    $test = getQuote($json);

        echo("Test: ");
        print_r($test);

    ?>

I've also tried MySQLi, same deal, same error. According to MySQL's documentation, scant as it is, I'm calling the function properly and passing the variables correctly. (PHP's documentation on such is virtually non-existent; their examples all amount to "SELECT * from tblFoo WHERE bar=4.")

The error is that it treats chedder as a column name rather than as the variable: "Unknown column 'cheddar' in 'field list'". What am I missing?

I could do this in SQLServer and ASP.NET without batting an eye, but MySQL/PHP is confusing the penguins out of me X_X (Not Linux penguins, just the zoo kind.)

PHP version is 5.3.17, MySQL is 5.1 in case either makes a difference. I think PHP 5.4.* has some relevant methods, but before I go through the hassle of changing my PHP version, I'd rather exhaust any 5.3.* options first.

I'm well-aware the answer is probably obvious, and know there's no one who'll outdo me for calling myself stupid once I learn what I'm doing.

Thank you again! This site is darned near my go-to before Google for questions ^_^ (And at least 2/3 of the results for any Google question I ask come from here anyway =-p )

EDIT: OK the MySQL works alone (SELECT from etc...) Turns out it wanted ' vs. `.

score 1 · Answer 1 · edited May 23 '17 at 12:12

1

The program is passing in a bogus query string due to SQL string injection and lack of placeholders ..

SELECT calculatePrice(Cheddar, Ham, Carrot);

.. is not valid SQL: those should be string literals, not [unbound] column identifiers.

Expanding: MySQL is expecting a Column or an Expression here and Cheddar, Ham, and Carrot are not columns in the current SQL statement. They are also not expressions (like 'Cheddar' or 1 + 41). This is why error message says "Unknown column 'cheddar'", as there is no such column bound in this statement.

While one approach to ensure the function gets string values would be to add 's in the generation of the SQL statement, there are better approaches described here.

Also, verify that it doesn't need to have a result column name as well, e.g select calculatePrice(..) as colName, which is required in at least SQL Server ..

edited May 23 '17 at 12:12

Community

1
1

answered Oct 19 '12 at 01:38

The column name is cheeseName, meatName, veggieName. Chedder's a piece of data. I've tried both with and without the `quotes` and the results are the same =-( The MySQL server's doing the math and returning the value to pass back to jQuery via JSON. – Janet Oct 19 '12 at 01:42
See the error message and inspect the *actual SQL query string used*. If the query can't be run "by hand" then it won't run from PHP either. There is no special context handling here. – Oct 19 '12 at 01:43
The query string, as var_dump'd to the screen: SELECT calculatePrice(`Chedder`, `Ham`, `Carrot`) The capitalization of the strings is identical to their entries in their respective tables. – Janet Oct 19 '12 at 01:52
@Janet Yes. Just like I put in my answer. It is invalid. Try running it "by hand" (in the mysql CLI or whatnot). That is, *run what I wrote*, which is what the code generates. Copy and paste. What error does it generate? – Oct 19 '12 at 01:52
Erf, same error. (And I apologize for misunderstanding your earlier comment ^_^;;; ) So MySQL hates something... – Janet Oct 19 '12 at 01:56
It hates invalid SQL. How does `SELECT f(C)` differ from `SELECT f('C')`? This is the same difference as exists between `SELECT calculatePrice(Cheddar, Ham, Carrot)` and `SELECT calculatePrice('Cheddar', 'Ham', 'Carrot')`. – Oct 19 '12 at 01:57

PHP pass variables to my own MySQL function--DB using variable as column name

1 Answers1