Using Post variables in SQL Query for Azure SQL db

[VexedDeveloper]

Hi all,

I am trying to use POST variables in my SQL query string:

 

I am running the PHP script on IIS and I am getting these errors:

var dumb of the $getresults:

bool(false)
Notice: Array to string conversion in C:\inetpub\wwwroot\phpinfo.php on line 27
Array
Warning: sqlsrv_fetch_array() expects parameter 1 to be resource, boolean given in C:\inetpub\wwwroot\phpinfo.php on line 28

Warning: sqlsrv_free_stmt() expects parameter 1 to be resource, boolean given in C:\inetpub\wwwroot\phpinfo.php on line 35

What am I doing wrong?

 

Thanks in advance.
 

<?php

    $serverName = "something.database.windows.net";
    $connectionOptions = array(
        "Database" => "something",
        "Uid" => "something",
        "PWD" => "something"
    );
    //Establishes the connection
    $conn = sqlsrv_connect($serverName, $connectionOptions);

    $tableName = 0;
    $columnName = 0;
    $tsql = null;
    if(isset($_POST['dbName'])) {

        $tableName = $_POST['dbName'];
        $columnName = $_POST['columnName'];

    }
    $tsql = "SELECT $columnName FROM $tableName";
    //"SELECT ProductName, ProductId FROM ProductData";// . $columnName .  "FROM "  . $tableName;
    $getResults= sqlsrv_query($conn, $tsql);
    var_dump($getResults);
    if ($getResults == FALSE)
        echo (sqlsrv_errors());
    while ($row = sqlsrv_fetch_array($getResults, SQLSRV_FETCH_ASSOC)) {
        echo($row['ProductName'] . "\r\n" . PHP_EOL);
        //echo($row['ProductId'] . "<br>" . PHP_EOL);
        //$row = sqlsrv_fetch_array($getResults, SQLSRV_FETCH_ASSOC);
        //echo ($row['ProductName'] . "\n" . PHP_EOL);
    }

    sqlsrv_free_stmt($getResults);

    ?>

 

[cyberRobot]

56 minutes ago, VexedDeveloper said:

Notice: Array to string conversion in C:\inetpub\wwwroot\phpinfo.php on line 27

You'll want to look into how sqlsrv_errors() returns the result. More information can be found here:
http://php.net/manual/en/function.sqlsrv-errors.php

56 minutes ago, VexedDeveloper said:

Warning: sqlsrv_fetch_array() expects parameter 1 to be resource, boolean given in C:\inetpub\wwwroot\phpinfo.php on line 28

Warning: sqlsrv_free_stmt() expects parameter 1 to be resource, boolean given in C:\inetpub\wwwroot\phpinfo.php on line 35

It sounds like the query failed. Did you try to echo out the query to see if it looks like you expect?

 

Side note: in case you're not doing this already, you'll want to look into using prepared statements for queries like the following:

$tsql = "SELECT $columnName FROM $tableName";

The values for $columnName and $tableName come from the user. Both have the potential for breaking the query or worse (hijacking it). It's very easy for a person with enough knowledge to modify what gets submitted through a POST variable.

Edited August 31, 2018 by cyberRobot

[requinix]

So if the form isn't submitted then you're going to

SELECT 0 FROM 0

That doesn't look good. But then the rest of it doesn't look good either: putting user-supplied values directly into a query is really, really bad.

What are you actually trying to do with this code? Why are the column and table names coming from a form?

[VexedDeveloper]

Hi all thanks for replying.

I did try and echo what you suggested but nothing comes out. Just those errors.

 

I am wanting to specify which column and table to query data from Unity. Send it to my php web app and then it queries th database and bring the result back down to unity.

[cyberRobot]

What are the specific errors?

[VexedDeveloper]

Those that I posted above in my first post. This is my first time coding in PHP. 

[cyberRobot]

Did you try echoing the following information to see if the query looks correct?

$tsql = "SELECT $columnName FROM $tableName";

echo $tsql;

If the query looks correct, do you have any alternate way of running the query. Do you have an database admin panel, for example? If so, does the query work there and give you the desired result(s)?

[VexedDeveloper]

Oh sorry I haven’t tried that will do when I get back I am away for the weekend. I do have a database admin panel and it queries fine and as expected. 

Edited August 31, 2018 by VexedDeveloper

[mac_gyver]

the biggest issues with your code (some of which have already been mentioned) are -

1) all the form processing code isn't inside the conditional statement detecting if a form has been submitted, so the part where the sql query is at can be executed when there is no post data to use. all the form processing code needs to be inside the conditional statement and if this is the only form processing code, you should instead detect if $_SERVER['REQUEST_METHOD'] == 'POST'

2) sqlsrv_errors() returns an array. you cannot echo an array. to dump the errors for debugging purposes, either use var_dump() or print_r(). also, your code needs only execute the rest of the database code if $getResults is a true value. the rest of the errors are 'follow on' errors because the query failed. you should also have some error handling for the database connection so that you only execute the query if the connection worked.

3) after you have detected that the form has been submitted, you must validate all input data before using it. this is doubly important if the form data specifies column/table names for an sql query statement, since no amount of escaping  of the values (it's not string data and it's not being used in a string context) or using a prepared query (you can only supply data values, not column/table names via prepared query place-holders) will protect against sql injection. for column/table names, you must either validate that each value is only and exactly a permitted column or table name or you must use some sort of id mapping, where you submit id values instead that get mapped to column/table names internally in the code so that only id values that have a valid mapping can be used.

[VexedDeveloper]

Hi all,

Thanks alot for input and teachings. I will work at it and post what I have after all suggestions and see if I could improve it more. Thanks again

[florance]

thanks, that's helps me