如何获取在服务器端处理数据表中包含 (') 的 varchar？

Question

我正在尝试将QUOTE((函数添加到此查询中。https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_quote像这样。我该怎么做？这是一个从数据库加载列对象数据的查询

"SELECT `".implode("`, `", self::pluck($columns, 'db'))."`
FROM `$table`
$where
$order
$limit"

我正在尝试将数据库中的数据获取到数据表中。如果从数据库中选择的列没有('(，则代码有效。我的数据库中有一列包含varchar值，在该列中有用户条目；多么美好的一天！一个@？？ASD"；像这样的如果是这种情况，则不会加载数据表。我该怎么做？

<table id="example" class="display" style="width:100%">
<thead>
<tr>
<th>First name</th>
<th>Last name</th>
<th>Position</th>
<th>Office</th>
<th>Start date</th>
<th>Salary</th>
</tr>
</thead>
<tfoot>
<tr>
<th>First name</th>
<th>Last name</th>
<th>Position</th>
<th>Office</th>
<th>Start date</th>
<th>Salary</th>
</tr>
</tfoot>
</table>

<?php

/*
* DataTables example server-side processing script.
*
* Please note that this script is intentionally extremely simple to show how
* server-side processing can be implemented, and probably shouldn't be used as
* the basis for a large complex system. It is suitable for simple use cases as
* for learning.
*
* See http://datatables.net/usage/server-side for full details on the server-
* side processing requirements of DataTables.
*
* @license MIT - http://datatables.net/license_mit
*/

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Easy set variables
*/

// DB table to use
$table = 'datatables_demo';

// Table's primary key
$primaryKey = 'id';

// Array of database columns which should be read and sent back to DataTables.
// The `db` parameter represents the column name in the database, while the `dt`
// parameter represents the DataTables column identifier. In this case simple
// indexes
$columns = array(
array( 'db' => 'first_name', 'dt' => 0 ),
array( 'db' => 'last_name',  'dt' => 1 ),
array( 'db' => 'position',   'dt' => 2 ),
array( 'db' => 'office',     'dt' => 3 ),
array(
'db'        => 'start_date',
'dt'        => 4,
'formatter' => function( $d, $row ) {
return date( 'jS M y', strtotime($d));
}
),
array(
'db'        => 'salary',
'dt'        => 5,
'formatter' => function( $d, $row ) {
return '$'.number_format($d);
}
)
);

// SQL server connection information
$sql_details = array(
'user' => '',
'pass' => '',
'db'   => '',
'host' => ''
);


/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP
* server-side, there is no need to edit below this line.
*/

require( 'ssp.class.php' );

echo json_encode(
SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
);


static function simple ( $request, $conn, $table, $primaryKey, $columns )
{
$bindings = array();
$db = self::db( $conn );
// Build the SQL query string from the request
$limit = self::limit( $request, $columns );
$order = self::order( $request, $columns );
$where = self::filter( $request, $columns, $bindings );
// Main query to actually get the data
$data = self::sql_exec( $db, $bindings,
"SELECT `".implode("`, `", self::pluck($columns, 'db'))."`
FROM `$table`
$where
$order
$limit"
);
// Data set length after filtering
$resFilterLength = self::sql_exec( $db, $bindings,
"SELECT COUNT(`{$primaryKey}`)
FROM   `$table`
$where"
);
$recordsFiltered = $resFilterLength[0][0];
// Total data set length
$resTotalLength = self::sql_exec( $db,
"SELECT COUNT(`{$primaryKey}`)
FROM   `$table`"
);
$recordsTotal = $resTotalLength[0][0];
/*
* Output
*/
return array(
"draw"            => isset ( $request['draw'] ) ?
intval( $request['draw'] ) :
0,
"recordsTotal"    => intval( $recordsTotal ),
"recordsFiltered" => intval( $recordsFiltered ),
"data"            => self::data_output( $columns, $data )
);
}

Answer 1

只需将字符串中的单引号替换为两个单引号。

$string = " what a lovel'y day!A@??ASD ";
$replace = str_replace("'", "''", $string);

这应该行得通。