MySQL / PHP的 - 将数字引号打破任何疑问？ [英] MySQL/PHP - Would putting quotes around numbers break any queries?

问题描述

我想有一个通用的PHP函数，它根据函数的参数建立一个mysql查询。由于它是通用的，查询有时可能会要求id = 123或者name ='Bob'。我用数字引号测试了一些查询，甚至像 WHERE id> '50'+ 7 ，它的工作，但我怀疑这不会造成麻烦的道路上。我猜如果这真的是一个全功能的应用程序，它应该能够处理日期和其他数据类型。那么安全形成这些查询的最好方法是什么？

解决方案

查询支持mySQL。值将被处理的方式将取决于与其进行比较的字段的类型。如果需要的话，它们会被自动转换。

另外，您可能希望查看提供准备语句的数据库包装器，如 PDO 。除了其他的好处之外，他们会自己处理报价和转发数据。

手册：

 <？php 
 / *通过绑定PHP变量执行准备语句* / 
 $ calories = 150; 
 $ color ='red'; 
 $ sth = $ dbh-> prepare（'SELECT name，color，calories 
 FROM fruit 
 WHERE calories<：calories AND color =：color'）; 
 $ sth-> bindValue（'：calories'，$ calories，PDO :: PARAM_INT）; 
 $ sth-> bindValue（'：color'，$ color，PDO :: PARAM_STR）; 
 $ sth-> execute（）; 
？> 
  

I want to have a generic PHP function that builds a mysql query based on the parameters of the function. Since it is generic the query may sometimes ask for id=123 or name='Bob'. I test out some queries with quotes around numbers, even stuff like WHERE id > '50' + 7 and it worked but I have my doubts that this won't cause trouble down the road. I guess if this is really an all purpose function it should be able to handle dates and whatever other datatypes there are. So what would be the best way to form these queries safely?

解决方案

Quotes around values are fine for any type as long as your query sticks to mySQL. The way the values will be treated will depend on the type of the field it's compared against. If necessary, they will be converted automatically.

As an aside, you may want to look into database wrappers that offer prepared statements like PDO. Apart from other advantages, they will take care of the quoting - and the escaping of incoming data - themselves.

An example from the manual:

<?php
/* Execute a prepared statement by binding PHP variables */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->bindValue(':calories', $calories, PDO::PARAM_INT);
$sth->bindValue(':colour', $colour, PDO::PARAM_STR);
$sth->execute();
?>

这篇关于MySQL / PHP的 - 将数字引号打破任何疑问？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！