清理SQL“不正确的字符串值"来自PHP的错误 [英] Cleaning SQL "Incorrect string value" Error from PHP

问题描述

我似乎已经把这个问题解答了一百万遍了，但是每个人似乎都想解决数据库中的问题.我不.解析大型文本文件，选择所需内容并将其插入数据库时​​，出现此错误.在大约24,000行中，有30个左右的行中包含无效字符.

I've seem this question a million times, but everyone seems to want to solve the problem in the database. I do not. I'm getting this error when parsing a large text file, picking out what I need and inserting it into my database. Out of 24 thousand rows or so, 30 or so have invalid characters in them.

以下是错误的示例，后面是引起该错误的查询:

Here is an example of the error, followed by the query that caused it:

[查询错误:不正确的字符串值:第1行的'company'列的'\ xEF \ xBC \ x89']

[插入到mac_address_db_new(十六进制，公司)VALUES('0847D0'，'诺基亚上海贝尔有限公司)')]]

我要插入的字符串中的坏"字符可能不会完全进入浏览器，因此您可能看不到该字符.

The 'bad' characters in the string I'm inserting are probably not going to make it all the way to your browser, so you likely can't see the character.

但是我正在寻找一个更简单的解决方案.在尝试将它们发送到MySQL之前，有没有办法在PHP中过滤掉这些字符?我已经使用以下函数进行了过滤: trim()， mb_convert_encoding()(至UTF-8)， htmlentities()，〜某些情况相关功能〜，最后是 mysqli_real_escape_string().但是仍然有坏角色通过.我无法控制文本文件输入.

But I'm looking for a simpler solution. Is there a way I can filter out these characters in PHP before I try to send them to MySQL? I already filter with the following functions: trim( ), mb_convert_encoding( ) (to UTF-8), htmlentities( ), ~ some case-related functions ~, and finally mysqli_real_escape_string( ). But still bad characters are getting through. I have no control over the text file input.

推荐答案

我终于找到了解决方案.我放弃了 mb_convert_encoding()函数，而是使用了 iconv()，特别是:

I finally found a solution. I ditched the mb_convert_encoding( ) function and instead used iconv( ), specifically this:

$ output = iconv('UTF-8'，"ISO-8859-1//IGNORE"，$ input);

显然，"//IGNORE"部分非常重要.在将其发送到数据库之前，这会悄悄地从字符串中删除这些不良"字符.

Apparently the "//IGNORE" part is very important. This quietly strips these 'bad' characters out of my string before I send it off to the database.

这篇关于清理SQL“不正确的字符串值"来自PHP的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！