Page 1 of 1

converting a php script for mySql to microsoft sql

Posted: Wed Apr 30, 2014 11:58 am
by ScoobyDoobie
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.

Code: Select all

[syntax=php][syntax=php]//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";
}
?>[/syntax][/syntax]

Re: converting a php script for mySql to microsoft sql

Posted: Wed Apr 30, 2014 12:53 pm
by requinix
There is. Besides the work of adding the extension, which is either SQLSRV (Windows) or some version of mssql for your PHP version (not Windows), you'd have to find every one of those mysql_* function calls and replace them with the right sqlsrv/mssql function call.
And then make sure that whatever things you have that are *shudder* passing SQL queries to this script and make sure they're writing valid T-SQL queries.

Re: converting a php script for mySql to microsoft sql

Posted: Wed Apr 30, 2014 6:16 pm
by Christopher
First, the horror if passing SQL to a page that is only lightly authentication protected seems foolish. I assume that you only access this page with HTTPS to protect your key. I'd recommend developing a structured was to pass query information in a non-SQL data that can be easily validated. And pre-authenticating. And requiring HTTPS.

I would recommend porting this code to PDO. That would be very easy. Then all you have to do is change the connection information. That is assuming that you are using standard SQL syntax.