Jump to content
VexedDeveloper

Using Post variables in SQL Query for Azure SQL db

Recommended Posts

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);

    ?>

 

Share this post


Link to post
Share on other sites
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 by cyberRobot

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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)?

Share this post


Link to post
Share on other sites

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 by VexedDeveloper

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.