I have an old PHP code that has mysql in it.

我有一个包含mysql的PHP代码。

It gets an array from a SELECT statement, adds it to a JSON object, as a property and echoes the encoded JSON.

它从SELECT语句中获取一个数组，将其作为属性添加到JSON对象中，并对经过编码的JSON进行响应。

I changed it around to use mysqli, but when I try to get the rows, and create an array out of them, it just returns nothing.

我将它修改为使用mysqli，但是当我尝试获取这些行并从中创建一个数组时，它将不返回任何内容。

Here's the old mysql code:

下面是旧的mysql代码:

$con = mysql_connect('host','account','password');
if (!$con)
{
    //log my error
};

mysql_select_db("database_name", $con);
mysql_set_charset('utf8');
$sql = "SELECT field1 as Field1, field2 as Field2 from table where ID = '".$parameter."'";
$query = mysql_query($sql);
$results = array();
while($row = mysql_fetch_assoc( $query ) )
{
    $results[] = $row;
}
return $results;

Version1: Here's the new one that I tried writing:

这是我尝试写的新版本:

$con = mysqli_connect('host','account','password','database_name');
$sql = "SELECT field1 as Field1, field2 as Field2 from table where ID = '".$parameter."'";
$results = array();
if($result=mysqli_query($con, $sql))
{
    while ($row=mysqli_fetch_assoc($result)) 
    {
        $results[] = $row;
    }
    return $results;
}
else
{
    //error
}

Version2: Second thing I tried, which only returns 1 ROW:

我尝试的第二件事，它只返回一行:

...same as above until $sql
if($result=mysqli_query($con,$sql))
{
    $row=mysqli_fetch_assoc($result);
    return $row;
}

Version3: Or I tried to completely mirror the mysql structure like this:

或者我尝试完全镜像mysql结构:

$sql = "SELECT ...";
$query = mysqli_query($con, $sql);
$results = array();
while($row = mysqli_fetch_assoc( $query ) )
{
    $results[] = $row;
}
return $results;

Wrapping the resulting array into the JSON:

将结果数组封装到JSON中:

$obj = new stdClass();
$obj->Data = $results;
$obj->ErrorMessage = '';
die(json_encode($obj)); //or echo json_encode($obj);

None of the mysqli version are working, so I was thinking there might be an important change in the way these arrays are created.

sqmyli版本中没有一个是有效的，所以我认为在创建这些数组的方式上可能有一个重要的改变。

Any tips on what could be wrong on the first mysqli example?

关于第一个mysqli的例子有什么问题吗?

With Version2 I can tell that the SQL connection is there, and I can at least select a row. But it's obviously only one row, than it returns it. It makes me think, that building up the array is the source of the problem, or it's regarding the JSON object...

通过Version2，我可以看出SQL连接在那里，我至少可以选择一行。但显然它只返回一行。它使我想到，构建数组是问题的根源，或者是关于JSON对象……

LATER EDIT: OK! Found a working solution.

后来编辑:好的!找到了一个工作的解决方案。

ALSO, I played around with the data, selected a smaller chunk, and it suddenly worked. Lesson from this: the function is not responding the same way for 40 rows or for 5 rows. Does it have something to do with a php.ini setting? Or could there be illegal characters in the selection? Could it be that the length of a 'Note' column (from the db) is too long for the array to handle?

此外，我还摆弄了一下数据，选择了一个更小的数据块，它突然起作用了。由此得出的教训是:对于40行或5行，函数的响应方式不同。它和php有关系吗?ini设置?或者选择中会有非法字符吗?是否可能是“Note”列(来自db)的长度太长，数组无法处理?

Here's the working chunk of code, that selects some rows from the database, puts them into an array, and then puts that array into an object that is encoded into JSON at the end, with a statusmessage next to it. Could be improved, but this is just for demo.

这是代码的工作块，它从数据库中选择一些行，将它们放入一个数组中，然后将该数组放入一个被编码为JSON的对象中，并在其旁边有一个statusmessage。可以改进，但这只是为了演示。

$con = mysqli_connect('host','username','password','database_name');
if (!$con)
{
    $errorMessage = 'SQL connection error: '.$con->connect_error;
    //log or do whatever.
};
$sql = "SELECT Field1 as FieldA, field2 as FieldB, ... from Table where ID='something'";

$results = array();

if($result = mysqli_query($con, $sql))
{
    while($row = mysqli_fetch_assoc($result))
    {
        $results[] = $row;
    }
}
else 
{
    //log if it failed for some reason
    die();
}

$obj->Data = $results;
$obj->Error = '';
die(json_encode($obj));

Question is: how can I overcome the issue regarding the size of the array / illegal characters (if that's the case)?

问题是:如何克服关于数组/非法字符大小的问题(如果是这样的话)?

1 个解决方案

#1

Your "Version 1" seems to be correct from a PHP perspective, but you need to actually handle the errors - both when connecting and when performing the query. Doing so would have told you that you don't actually query a table, you're missing FROM tablename in the query.

从PHP的角度来看，您的“版本1”似乎是正确的，但是您需要实际处理错误——在连接时和执行查询时都是如此。这样做会告诉您，您实际上并没有查询表，而是在查询中缺少tablename。

Use mysqli_connect_error() when connecting, and mysqli_error($con) when querying to get back the actual errors. General PHP error-reporting might also help you.

连接时使用mysqli_connect_error()，查询时使用mysqli_error($con)获取实际的错误。一般的PHP错误报告也可以帮助您。

The code below assumes that $parameter is defined prior to this code.

下面的代码假设在此代码之前定义了$parameter。

$con = mysqli_connect('host','account','password','database_name');
if (mysqli_connect_errno())
    die("An error occurred while connecting: ".mysqli_connect_error());

$sql = "SELECT field1 as Field1, field2 as Field2 
        FROM table
        WHERE ID = '".$parameter."'";

$results = array();
if ($result = mysqli_query($con, $sql)) {
    while ($row = mysqli_fetch_assoc($result)) {
        $results[] = $row;
    }
    return $results;
} else {
    return mysqli_error($con);
}

Error-reporing

Adding

添加

error_reporting(E_ALL);
ini_set("display_errors", 1);

at the top of your file, directly after <?php would enable you to get the PHP errors.

在您的文件的顶部，直接在

NOTE: Errors should never be displayed in a live environment, as it might be exploited by others. While developing, it's handy and eases troubleshooting - but it should never be displayed otherwise.

注意:错误不应该显示在活动环境中，因为它可能被其他人利用。在开发过程中，它很方便，可以简化故障排除——但是不应该显示它。

Security

You should also note that this code is vulnerable to SQL-injection, and that you should use parameterized queries with placeholders to protect yourself against that. Your code would look like this with using prepared statements:

您还应该注意到，这段代码容易受到sql注入的影响，您应该使用带占位符的参数化查询来保护自己不受sql注入的影响。使用准备好的语句，您的代码看起来是这样的:

$con = mysqli_connect('host','account','password','database_name');
if (mysqli_connect_errno())
    die("An error occurred while connecting: ".mysqli_connect_error())

$results = array();

if ($stmt = mysqli_prepare("SELECT field1 as Field1, field2 as Field2 
                            FROM table
                            WHERE ID = ?")) {
    if (mysqli_stmt_bind_param($stmt, "s", $parameter)) {
        /* "s" indicates that the first placeholder and $parameter is a string */
        /* If it's an integer, use "i" instead */
        if (mysqli_stmt_execute($stmt)) {
            if (mysqli_stmt_bind_result($stmt, $field1, $field2) {
                while (mysqli_stmt_fetch($stmt)) {
                    /* Use $field1 and $field2 here */
                }
                /* Done getting the data, you can now return */
                return true;
            } else {
                error_log("bind_result failed: ".mysqli_stmt_error($stmt));
                return false;
            }
        } else {
            error_log("execute failed: ".mysqli_stmt_error($stmt));
            return false;
        }
    } else {
        error_log("bind_param failed: ".mysqli_stmt_error($stmt));
        return false;
    }
} else {
    error_log("prepare failed: ".mysqli_stmt_error($stmt));
    return false;
}

References

http://php.net/mysqli.prepare
http://php.net/mysqli.prepare
How can I prevent SQL injection in PHP?
如何防止PHP中的SQL注入?

PHP mysqli_fetch_assoc不返回正确的值。

1 个解决方案

#1

Error-reporing

Security

References

更多相关文章

随机推荐