[RESOLVED] Addslashes and MSSQL

[badgoat]

Hello!

I have been trying to optimize a script which will allow me to enter a name with an apostrophe to an MSSQL database, and I am having no luck. I spoke with the person who manages the MSSQL server and he says that the server will not allow apostrophes to be added. Is he correct? Or is it my code?

$name=addslashes($_POST['name']); 

$sqlquery = "INSERT INTO table (name) VALUES(addslashes($_POST['name']) ."')";

<tr>
    <td>Name</td>       

    <td>'.$_POST['name'].'</td>
</tr>

EDIT

I should note that I have magic_quotes_gpc turned off as well as magic_quotes_runtime.

[devinemke]

$sql = "INSERT INTO table SET name = '" . addslashes($_POST['name']) . "'";

[badgoat]

That was the way I would type a query to insert into a MySQL server, but the syntax for the MSSQL server doesn't like it that way, which is why I used the above format 🙁

Warning: MS SQL message: Incorrect syntax near the keyword 'SET'. (severity 15)

EDIT

I found a snippet of info on another site which says that MSSQL sees a single apostrophe as the end of the string, so if you typed two apostrophes, it would work... And sure enough, it worked when I entered a name like Bill O''Reilly. I checked the entry in the table and the name is entered correctly.

Now, is there a way to avoid having to type a double apostrophe anytime there's a name which has an apostrophe in it?

[badgoat]

Alright.. I found a piece of code in the code library which evidently repairs the problem I am experiencing through a function. I am not super familiar with functions, and certainly not ones which appear (to me) to be as complex as the code I posted below. Looking thru it, I am not sure if this line is correct?

$params = split(",", $strQuery);

And how would I implement this into my script? What would I need to change in the function? Where would I place it?

function checkApostrophes(&$strQuery)	{
	//Remove white space
	$strQuery = trim($strQuery);
	$params = split(",", $strQuery);
	$buffer = "";
	foreach($params as $param)	{
		$param = trim($param);
		if (substr_count($param, "'") > 0)	{
			//replace double all quotes in data
			$param = substr($param, 0, strpos($param, "'"))."'".str_replace("'", "''", substr( $param, strpos($param, "'") + 1, -1 ))."'";
		}
		$buffer .= $param.", ";
	}
	//remove final "," and "\"s
	$strQuery = str_replace("\\", "", substr($buffer, 0,  -2));
}

[MarkR]

addslashes is not the right escaping function for mssql, nor mysql, or any other database.

addslashes is not appropriate for any common usage, and therefore should not generally be used.

Use the appropriate database-specific escaping function instead, or use prepared statements.

Mark

[badgoat]

Similar to this function below?

function escapeSingleQuotes($string){
//escapse single quotes
$singQuotePattern = "'";
$singQuoteReplace = "''";
return(stripslashes(eregi_replace($singQuotePattern, $singQuoteReplace, $string)));
}

A question about this... What does $string represent? And what would be plgged in there to allow the function to work?

[Roger_Ramjet]

$string is the value string that you want to clean up before including it in the query.

[badgoat]

Hi Roger,

I have tried changing the $string value to the $name variable in my little code snippet with no luck.. Also tried '$_POST[name]' and with both, I still get error messages... I even tried making it an include and including the function, and that didn't work either in either of the two configurations..

[Roger_Ramjet]

In a function declaration, the var name inside the brackets is just a placeholder for the values that you will supply when you call the function later.

// $string is just a placeholder
function escapeSingleQuotes($string){
//escapse single quotes
$singQuotePattern = "'";
$singQuoteReplace = "''";
return(stripslashes(eregi_replace($singQuotePattern, $singQuoteReplace, $string)));
}

// we now supply a value for string when we call the function

$name = escapeSingleQuotes($_POST['name']);

// our name should now be query-ready even if it's Mr John O'Shea

REMEMBER, you must use double-quotes for the whole query string and single quotes around any strings inside the query for this to work.

[badgoat]

Thank you kindly Roger, that makes perfect sense, and worked like a charm!