php - SQL Server Update & Replace Spam in "text" and "ntext" fields -

first , foremost, thank time taking @ issue.

an old database table has on 14k spam links injected it, many of in text , ntext fields. have written sql query runs , updates fields not "text" or "ntext" type, unfortunately not update "text" or "ntext" fields @ all.

brief information database: running on iis7, sql server 2008, , php enabled (version 5.3). unfortunately have limited capability update database directly or control panel (otherwise have been handled swiftly) writing script in php automatically update compromised tables. script in form runs without error, not have updates in text or ntext fields.

the script follows:

//basic db connection $conn = database_info; $sql = "select * pages_test_only";   $result = sqlsrv_query($conn, $sql);  //loop scrub each table foreach(sqlsrv_field_metadata($result) $fieldmetadata)  {     //the loop here updates each section of spam (starting </title>) "" (empty/null)     //and leaves other content intact.  double quotes in spam escaped (\").      //text update - if field type 'text'     if ($fieldmetadata['type'] == -1)      {      $sqlupdate = "update pages_test_only set ".$fieldmetadata['name']." = cast(replace(cast(".$fieldmetadata['name']." nvarchar(6000)), '</title><div style=\"display:block; text-indent:-5670px;\"><a href=\"http://buy-cialis-onlineusa.com\">generic cialis</a></div>', '') text";     }     //ntext update - if field type 'ntext'     elseif ($fieldmetadata['type'] == -10)      {      $sqlupdate = "update pages_test_only set ".$fieldmetadata['name']." = cast(replace(cast(".$fieldmetadata['name']." nvarchar(6000)), '</title><div style=\"display:block; text-indent:-5670px;\"><a href=\"http://buy-cialis-onlineusa.com\">generic cialis</a></div>', '') ntext";     }     //any other update - if field contains spam, of other type [this working]     else     {     $sqlupdate = "update pages_test_only set ".$fieldmetadata['name']." = replace(".$fieldmetadata['name'].", '</title><div style=\"display:block; text-indent:-5670px;\"><a href=\"http://buy-cialis-onlineusa.com\">generic cialis</a></div>', '')";     }      //send db     $res = sqlsrv_query($conn, $sqlupdate);  } 

i aware converting text , ntext fields nvarchar permanently ideal, these purposes need table structure remain when script completes.

additionally, if know of way pull in column metadata without having select in sql server 2008, appreciate information speed query actual table.

please let me know issues see, , again thank time.

edit:

while major issue still exists, able pull in field name , type query:

$sql = "select column_name, data_type information_schema.columns table_name = 'pages_test_only'"; $result = sqlsrv_query($conn, $sql); 

it applied in while loop:

while($row = sqlsrv_fetch_array($result)){     echo $row['column_name']."<br />";     echo $row['data_type']."<br />";      //update - text type     if ($row['data_type'] == 'text')      {      echo "this column text column.<br />";     $sqlupdate = "update pages_test_only set ".$row['column_name']." = cast(replace(cast(".$row['column_name']." nvarchar(1000)), '</title><div style=\"display:block; text-indent:-5670px;\"><a href=\"http://buy-cialis-onlineusa.com\">generic cialis</a></div>', 'test') text";     }     //update - ntext type      elseif ($row['data_type'] == 'ntext')      {      echo "this column ntext column.<br />";     $sqlupdate = "update pages_test_only set ".$row['column_name']." = cast(replace(cast(".$row['column_name']." nvarchar(1000)), '</title><div style=\"display:block; text-indent:-5670px;\"><a href=\"http://buy-cialis-onlineusa.com\">generic cialis</a></div>', 'test') ntext";     }     //update - other type     else     {     $sqlupdate = "update pages_test_only set ".$row['column_name']." = replace(".$row['column_name'].", '</title><div style=\"display:block; text-indent:-5670px;\"><a href=\"http://buy-cialis-onlineusa.com\">generic cialis</a></div>', 'test')";     } $res = sqlsrv_query($conn, $sqlupdate); } 

i'm bit embarrassed, issue having syntax. forgot parenthesis on end of each sql statement. being said, here final, working set of updates:

//basic connection information $servername = "dbhost.etc"; //servername\instancename $connectioninfo = array( "database"=>"db_name", "uid"=>"db_user", "pwd"=>"db_pass"); $conn = sqlsrv_connect( $servername, $connectioninfo);  //connection check if( $conn )  {  echo "connection established.<br /><br />"; } else {  echo "connection not established.<br />";  die( print_r( sqlsrv_errors(), true)); }  //this selects column name , type table $sql = "select column_name, data_type information_schema.columns table_name = 'table_name'"; $result = sqlsrv_query($conn, $sql);  //a loop set each column checks data type , casts/updates accordingly while($row = sqlsrv_fetch_array($result)) {      //update - text type     if ($row['data_type'] == 'text')      {      $sqlupdate = "update table_name set ".$row['column_name']." = cast(replace(cast(".$row['column_name']." nvarchar(max)), 'string', 'replacement') text)";     }     //update - ntext type      elseif ($row['data_type'] == 'ntext')      {      $sqlupdate = "update table_name set ".$row['column_name']." = cast(replace(cast(".$row['column_name']." nvarchar(max)), 'string', 'replacement') ntext)";     }     //update - other type     else     {     $sqlupdate = "update table_name set ".$row['column_name']." = replace(".$row['column_name'].", 'string', 'replacement')";     }       $res = sqlsrv_query($conn, $sqlupdate);  }