Converting a php script for mySql to microsoft sql

PHP
1

I have a script that is designed to work with mysql and i need it to work with microsoft sql. I have already built a web app using mvc and have it connecting to the database. i have also built a mobile app using a software called App inventor 2 shich just uses code blocks. I need the mobile app to connect to the same database but in order to do so i have to use a php script but the problem is this script is wrote for mysql and i am using microsoft sql. I have never used php before and am wondering is there much involved to change the script so it will work with microsoft sql.

//DATABASE DETAILS//
$DB_ADDRESS="";
$DB_USER="";
$DB_PASS="";
$DB_NAME="";

//SETTINGS//
//This code is something you set in the APP so random people cant use it.
$SQLKEY="randomkey";

/************************************CONFIG****************************************/

//these are just in case setting headers forcing it to always expire
header('Cache-Control: no-cache, must-revalidate');

error_log(print_r($_POST,TRUE));

if( isset($_POST['query']) && isset($_POST['key']) ){         //checks if the tag post is there and if its been a proper form post
  //2014/02/18: set content type to CSV (to be set here to be able to access this page also with a browser)
  header('Content-type: text/csv');

  if($_POST['key']==$SQLKEY){                                 //validate the SQL key
    $query=urldecode($_POST['query']);
    if(get_magic_quotes_gpc()){     //check if the worthless pile of crap magic quotes is enabled and if it is strip the slashes from the query
      $query=stripslashes($query);
    }
    $link = mysql_connect($DB_ADDRESS,$DB_USER,$DB_PASS);     //connect ot the MYSQL database
    mysql_select_db($DB_NAME,$link);                          //connect to the right DB
    if($link){
      $result=mysql_query($query);                            //runs the posted query (NO PROTECTION FROM INJECTION HERE)
      if($result){
        if (strlen(stristr($query,"SELECT"))>0) {          //tests if its a select statemnet
          $num_fields = mysql_num_fields($result);            //collects the rows and writes out a header row
          $headers = array();
          for ($i = 0; $i < $num_fields; $i++) {
            $headers[] = mysql_field_name($result , $i);
          }
          $outstream = fopen("php://temp", 'r+');             //opens up a temporary stream to hold the data
          fputcsv($outstream, $headers, ',', '"');
          while ($row = mysql_fetch_row($result)){
            fputcsv($outstream, $row, ',', '"');
          }
          rewind($outstream);
          fpassthru($outstream);
          fclose($outstream);
          // echo $csv; //writes out csv data back to the client
        } else {
          header("HTTP/1.0 201 Rows");
          echo "AFFECTED ROWS: ".mysql_affected_rows($link); //if the query is anything but a SELECT it will return the number of affected rows
        }
      } else {
        header("HTTP/1.0 400 Bad Request");                  //send back a bad request error
        echo mysql_errno($link).": ".mysql_error($link);     // errors if the query is bad and spits the error back to the client
      }
      mysql_close($link);                                    //close the DB
    } else {
      header("HTTP/1.0 400 Bad Request");
      echo "ERROR Database Connection Failed";               //reports a DB connection failure
    }
  } else {
     header("HTTP/1.0 400 Bad Request");
     echo "Bad Request";                                     //reports if the code is bad/
  }
} else {
        header("HTTP/1.0 400 Bad Request");
        echo "Bad Request";
}
?>
2

Hi ScoobyDoobie, welcome to the forums,

You’re in luck. PDO to the rescue!
http://www.php.net/manual/en/ref.pdo-dblib.php

3

Little bit of a heads up. Unless something has changed in the last year, there is no streaming cursor for MSSQL, PDO or not. The entire record set from a query will need to be loaded into memory before you can iterate over it, unlike most other drivers.

4

Having just a random key to protect that script is pretty dangerous. I could in theory use your app than find the outgoing http request and than drop your entire database fyi. You should be using a web service to expose data or something like oauth. What you are using now is totally unsecure.