SQL Server Update & Replace Spam in “text” 和 “ntext” 字段中 - SQL Server Update & Replace Spam in "text" and "ntext" fields

首先，感谢您抽出时间查看此问题。

一个旧的数据库表中注入了超过 14k 个垃圾邮件链接，其中许多链接位于文本和 ntext 字段中。我编写了一个SQL查询，该查询运行并更新任何不是"文本"或"ntext"类型的字段，但是不幸的是，它根本不更新"文本"或"ntext"字段。

有关数据库的简要信息：它在 IIS7 上运行，是 SQL Server 2008，并且启用了 PHP（版本 5.3）。不幸的是，我直接或从控制面板更新数据库的能力非常有限（否则这将得到迅速处理），所以我正在用 PHP 编写此脚本以自动更新受感染的表。此表单中的脚本运行没有错误，但是我在文本或 ntext 字段中没有任何更新。

脚本如下：

//Basic DB Connection
$conn = database_info;
$sql = "SELECT * FROM pages_test_only";  
$result = sqlsrv_query($conn, $sql);
//Loop to scrub each table
foreach(sqlsrv_field_metadata($result) as $fieldMetadata) 
{
    //The loop here updates each section of spam (starting with </title>) with "" (empty/null)
    //and leaves other content intact.  The double quotes in the spam are escaped ('").
    //Text update - if the field type is 'text'
    if ($fieldMetadata['Type'] == -1) 
    { 
    $sqlupdate = "UPDATE pages_test_only SET ".$fieldMetadata['Name']." = CAST(REPLACE(CAST(".$fieldMetadata['Name']." as nvarchar(6000)), '</title><div style='"display:block; text-indent:-5670px;'"><a href='"http://buy-cialis-onlineusa.com'">generic cialis</a></div>', '') AS text";
    }
    //Ntext update - if the field type is 'ntext'
    elseif ($fieldMetadata['Type'] == -10) 
    { 
    $sqlupdate = "UPDATE pages_test_only SET ".$fieldMetadata['Name']." = CAST(REPLACE(CAST(".$fieldMetadata['Name']." as nvarchar(6000)), '</title><div style='"display:block; text-indent:-5670px;'"><a href='"http://buy-cialis-onlineusa.com'">generic cialis</a></div>', '') AS ntext";
    }
    //Any other update - if the field contains the spam, but is of any other type [this is 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 to DB
    $res = sqlsrv_query($conn, $sqlupdate);
}

我知道将文本和 ntext 字段永久转换为 nvarchar 将是理想的，但是出于这些目的，我需要在脚本完成后保持表结构不变。

此外，如果您知道一种无需选择 SQL Server 2008 中的所有内容即可提取列元数据的方法，我将不胜感激，因为它将加快我对实际表的查询速度。

请让我知道您看到的问题，再次非常感谢您的时间。

编辑：

虽然主要问题仍然存在，但我只能使用此查询拉入字段名称和类型：

$sql = "SELECT COLUMN_NAME, DATA_TYPE FROM information_schema.columns WHERE TABLE_NAME = 'pages_test_only'";
$result = sqlsrv_query($conn, $sql);

它在 while 循环中应用：

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 is a text column.<br />";
    $sqlupdate = "UPDATE pages_test_only SET ".$row['COLUMN_NAME']." = CAST(REPLACE(CAST(".$row['COLUMN_NAME']." as nvarchar(1000)), '</title><div style='"display:block; text-indent:-5670px;'"><a href='"http://buy-cialis-onlineusa.com'">generic cialis</a></div>', 'test') AS text";
    }
    //update - ntext type 
    elseif ($row['DATA_TYPE'] == 'ntext') 
    { 
    echo "This column is a ntext column.<br />";
    $sqlupdate = "UPDATE pages_test_only SET ".$row['COLUMN_NAME']." = CAST(REPLACE(CAST(".$row['COLUMN_NAME']." as nvarchar(1000)), '</title><div style='"display:block; text-indent:-5670px;'"><a href='"http://buy-cialis-onlineusa.com'">generic cialis</a></div>', 'test') AS ntext";
    }
    //update - any 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);
}

我有点尴尬，但我遇到的问题是语法。我忘记了每个SQL语句末尾的括号。话虽如此，这里是最终的工作更新集：

//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 could not be established.<br />";
 die( print_r( sqlsrv_errors(), true));
}
//this selects ONLY the column name and type from the table
$sql = "SELECT COLUMN_NAME, DATA_TYPE FROM information_schema.columns WHERE TABLE_NAME = 'table_name'";
$result = sqlsrv_query($conn, $sql);
//a loop is set up for each column that checks for data type and 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']." as nvarchar(max)), 'string', 'replacement') AS text)";
    }
    //update - ntext type 
    elseif ($row['DATA_TYPE'] == 'ntext') 
    { 
    $sqlupdate = "UPDATE table_name SET ".$row['COLUMN_NAME']." = CAST(REPLACE(CAST(".$row['COLUMN_NAME']." as nvarchar(max)), 'string', 'replacement') AS ntext)";
    }
    //update - any other type
    else
    {
    $sqlupdate = "UPDATE table_name SET ".$row['COLUMN_NAME']." = REPLACE(".$row['COLUMN_NAME'].", 'string', 'replacement')";
    }

    $res = sqlsrv_query($conn, $sqlupdate);
}