插入 - PHP & SQL Server
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3498692/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
INSERT - PHP & SQL Server
提问by bobince
I don't know what is going on, but it just doesn't want to work.
我不知道发生了什么，但它只是不想工作。
I keep getting this error when I submit my form:
提交表单时，我不断收到此错误：
Array ( [0] => Array ( [0] => 22001 [SQLSTATE] => 22001 [1] => 8152 [code] => 8152 [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]String or binary data would be truncated. [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]String or binary data would be truncated. ) [1] => Array ( [0] => 01000 [SQLSTATE] => 01000 [1] => 3621 [code] => 3621 [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]The statement has been terminated. [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]The statement has been terminated. ) )
数组 ( [0] => 数组 ( [0] => 22001 [SQLSTATE] => 22001 [1] => 8152 [代码] => 8152 [2] => [Microsoft][SQL Server Native Client 10.0][SQL服务器]字符串或二进制数据将被截断。[message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]字符串或二进制数据将被截断。) [1] => Array ( [0] => 01000 [SQLSTATE] => 01000 [1] => 3621 [code] => 3621 [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]语句已终止。[消息] => [ Microsoft][SQL Server Native Client 10.0][SQL Server]语句已终止。))
Here's the PHPCode:
这是PHP代码：
<?php
$who = $_REQUEST["who"];
$what = $_REQUEST["what"];
$serverName = "xxx";
$uid = "xxx";
$pwd = "xxx";
$databaseName = "xxx";
$connectionInfo = array( "UID"=>$uid,
"PWD"=>$pwd,
"Database"=>$databaseName);
/* Connect using SQL Server Authentication. */
$conn = sqlsrv_connect( $serverName, $connectionInfo);
$tsql = "insert into Suggestions (Who, What, Votes) values ('$who','$what','10')";
/* Execute the query. */
$stmt = sqlsrv_query( $conn, $tsql);
if ( $stmt )
{
$something = "Submission successful.";
}
else
{
$something = "Submission unsuccessful.";
die( print_r( sqlsrv_errors(), true));
}
$output=$something;
/* Free statement and connection resources. */
sqlsrv_free_stmt( $stmt);
sqlsrv_close( $conn);
?>
And here's the HTML Form:
这是 HTML 表单：
<form action="startvoting.php" method="post" id="myform">
<ol>
<li>
<label for="name">Nickname</label>
<input id="who" name="who" class="text" />
</li>
<li>
<label for="message">What <strong>you</strong> Want</label>
<textarea id="what" name="what"></textarea>
</li>
<li class="buttons">
<input type="image" src="images/send.gif" class="send" />
<div class="clr"></div>
</li>
</ol>
</form>
Can someone please help me? I don't know what to do!
有人可以帮帮我吗？我不知道该怎么办！
Thank you
谢谢
UPDATE
更新
Here is the definitions:
以下是定义：
TABLE_QUALIFIER TABLE_OWNER TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME PRECISION LENGTH SCALE RADIX NULLABLE REMARKS COLUMN_DEF SQL_DATA_TYPE SQL_DATETIME_SUB CHAR_OCTET_LENGTH ORDINAL_POSITION IS_NULLABLE SS_DATA_TYPE
DB_11967_suggestions dbo Suggestions Who 12 varchar 1 1 1 12 1 1 YES 39
DB_11967_suggestions dbo Suggestions What 12 varchar 1 1 1 12 1 2 YES 39
DB_11967_suggestions dbo Suggestions Votes 4 int 10 4 0 10 1 4 3 YES 38
Sorry it's not properly formatted.
抱歉，它的格式不正确。
采纳答案by shox
I think you have an error in columns(fields) types , try insert just one character , then the submission successfully , try expand fields type .. , i.e. increase char num ...etc
我认为您在列（字段）类型中有错误，尝试只插入一个字符，然后提交成功，尝试扩展字段类型..，即增加字符数...等
回答by bobince
The error occurs when you input a text field with more than one character. The error message “String or binary data would be truncated” would imply that you have created a table whose text columns are limited to one character. That would happen if your CREATEstatement said they were CHARas opposed to CHAR(somenumber)or NVARCHAR(somenumber).
当您输入包含多个字符的文本字段时会发生此错误。错误消息“字符串或二进制数据将被截断”意味着您创建了一个表，其文本列限制为一个字符。如果您的CREATE声明说它们CHAR与CHAR(somenumber)或相对，就会发生这种情况NVARCHAR(somenumber)。
However, you've a bigger problem:
但是，你有一个更大的问题：
$tsql = "insert into Suggestions (Who, What, Votes) values ('$who','$what','10')";
You've forgotten to SQL-escape those text strings. If they contain the 'character your query breaks, and any attacker can execute arbitrary SQL by injecting it into the query. Pretty soon your database ends up defaced with malware links, or worse.
您忘记了对这些文本字符串进行 SQL 转义。如果它们包含'您的查询中断的字符，任何攻击者都可以通过将其注入查询来执行任意 SQL。很快，您的数据库就会被恶意软件链接破坏，或者更糟。
Bizarrely, the sqlsrvdrivers don't seem to give you a proper escaping function, but then just replacing 'with ''should be enough for SQL Server. However, you're much better off avoiding the issue by using parameterised queries:
奇怪的是，sqlsrv驱动程序似乎没有为您提供适当的转义功能，但是对于 SQL Server 而言，仅替换'with''就足够了。但是，您最好通过使用参数化查询来避免这个问题：
sqlsrv_query(
$conn,
'INSERT INTO Suggestions (Who, What, Votes) VALUES (?, ?, 10)',
array($who, $what)
);
