仍然运行脚本的PHP mssql_query警告

Question

So, I am doing a bunch of things parsing an XML from 1 server, writing stuff into another server and then updating the mssql db! The whole process appeared to run smoothly until I ran the script from the terminal for the sake of FUN! When I run it from the terminal, it throws in a bunch of warnings like:

PHP Warning:  mssql_query(): message: Incorrect syntax near 's'. (severity 15) in 
/Volumes/Data/Users/username/Desktop/createXML.php on line 375

PHP Warning:  mssql_query(): General SQL Server error: Check messages from the SQL 
Server (severity 15) in /Volumes/Data/Users/username/Desktop/createXML.php on line 375

PHP Warning:  mssql_query(): message: Unclosed quotation mark after the character 
string ';'. (severity 15) in /Volumes/Data/Users/username/Desktop/createXML.php on line 375

PHP Warning:  mssql_query(): General SQL Server error: Check messages from the SQL 
Server (severity 15) in /Volumes/Data/Users/pdwivedi/Desktop/createXML.php on line 375

PHP Warning:  mssql_query(): Query failed in /Volumes/Data/Users/username/Desktop
/createXML.php on line 375

Here is line 375:

$query = mssql_query("UPDATE table_name SET C_ITP_STATUS = '".$ITP_Status."', 
C_ITP_ERRORS = '". $ITP_Error ."' WHERE id = '".$ID."';"); 

The funny thing is that the query executes and I have an updated DB. But, it still shows these warnings when run from terminal. And I WANT TO get rid of them! I MUST user MS SQL!!

Have tried looking around for solutions, but people hardly use MS SQL with mySQL being so much better (at least in terms of being widely used). Any help?

FUNNY THING: When I ONLY connect to the DB and perform this query in a new php script, it works fine and there are no warnings. Not sure why its like this!

RESOLVED: I didnt care to test my input parameters (pretty lame) in to the string BECAUSE I was super confident about what I was doing! ALWAYS ESCAPE SPECIAL CHARS no matter how confident you are (just shouting out loud)!!

Answer 1

It sounds to me like one of your input strings might contain a quote, and this is messing up the query. Your errors also indicate this. You should always treat all possible user input as tainted, and make it a habit to sanitize them every time, even if you don't think you need to.

I created a new php script and hard coded the 3 parameters and the query runs fine!

This also leads me to believe that there is a quote or special character somewhere in your variables that is messing up the query. You would want to use mysql_real_escape_string() to correct this.

$ITP_Status = mysql_real_escape_string($ITP_Status);
$ITP_Error = mysql_real_escape_string($ITP_Error);
$ID = mysql_real_escape_string($ID);
$query = mssql_query("UPDATE table_name SET C_ITP_STATUS = '".$ITP_Status."', C_ITP_ERRORS = '". $ITP_Error ."' WHERE id = '".$ID."';"); 

It should also be noted that you are using the old MySQL functions. The new MySQLi functions are the replacement, and what you should be using at a bare minimum.

You mention MS SQL. If you plan on using that, you cannot use the MySQLi functions. In that case it is recommended that you use the PDO interface, which will work for both MySQL and MS SQL. Many recommend PDO over MySQLi even if you are only using MySQL.