update - php sql注入




如何检查一个值是否已经存在以避免重复? (12)

为了保证唯一性,你需要添加一个唯一的约束。 假设你的表名是“urls”,列名是“url”,你可以用这个alter table命令添加唯一的约束:

alter table urls add constraint unique_url unique (url);

如果你的表已经有了重复的URL,alter table可能会失败(谁真的知道MySQL)。

我有一个网址表,我不想要任何重复的网址。 如何使用PHP / MySQL检查给定的URL是否已经在表中?


你可以做这个查询:

SELECT url FROM urls WHERE url = 'http://asdf.com' LIMIT 1

然后检查mysql_num_rows() == 1是否存在。


如果你不想有重复,你可以做以下事情:

如果多个用户可以向数据库插入数据,@Jeremy Ruten建议的方法可能会导致错误 :执行检查后,某人可以向表中插入类似的数据。


如果你只是想确保没有重复,那么添加一个唯一的索引到url字段,这样就不需要明确地检查url是否存在,只要插入正常,如果它已经存在,然后插入失败,重复键错误。


我不知道MySQL的语法,但所有你需要做的就是包装你的INSERT IF语句,将查询表,并查看是否具有给定的URL存在,如果存在 - 不插入新的记录。

如果MSSQL你可以这样做:

IF NOT EXISTS (SELECT 1 FROM YOURTABLE WHERE URL = 'URL')
INSERT INTO YOURTABLE (...) VALUES (...)

如果你想在表中插入urls,但只有那些不存在的,你可以在列上添加一个UNIQUE约束,在你的INSERT查询中添加IGNORE,这样你就不会出错。

示例:INSERT IGNORE INTO urls URL ='url-to-insert'


$url = "http://www.scroogle.com";

$query  = "SELECT `id` FROM `urls` WHERE  `url` = '$url' ";
$resultdb = mysql_query($query) or die(mysql_error());   
list($idtemp) = mysql_fetch_array($resultdb) ;

if(empty($idtemp)) // if $idtemp is empty the url doesn't exist and we go ahead and insert it into the db.
{ 
   mysql_query("INSERT INTO urls (`url` ) VALUES('$url') ") or die (mysql_error());
}else{
   //do something else if the url already exists in the DB
}

使列成primary key


要回答你最初的问题,检查是否有重复的最简单的方法是对你要添加的东西运行一个SQL查询!

例如,你是否想检查表links的网址http://www.example.com/ ,那么你的查询将看起来像

SELECT * FROM links WHERE url = 'http://www.example.com/';

你的PHP代码看起来像这样

$conn = mysql_connect('localhost', 'username', 'password');
if (!$conn)
{
    die('Could not connect to database');
}
if(!mysql_select_db('mydb', $conn))
{
    die('Could not select database mydb');
}

$result = mysql_query("SELECT * FROM links WHERE url = 'http://www.example.com/'", $conn);

if (!$result)
{
    die('There was a problem executing the query');
}

$number_of_rows = mysql_num_rows($result);

if ($number_of_rows > 0)
{
    die('This URL already exists in the database');
}

我已经在这里写了这个longhand,所有的连接到数据库,等等。你可能已经有一个连接到数据库,所以你应该使用它,而不是开始一个新的连接(替换$conn mysql_query命令,并删除与mysql_connectmysql_select_db做的东西)

当然,还有其他连接数据库的方法,比如PDO,或者使用ORM,或者类似的方法,所以如果你已经在使用这些方法了,那么这个答案可能就没有关系了(可能有点超出范围与此相关的答案!)

但是,MySQL提供了很多方法来防止这种情况发生。

首先,您可以将某个字段标记为“唯一”。

比方说,我有一张桌子,我只想存储所有链接到我的网站,上次访问他们的网址。

我的定义可能看起来像这样:

CREATE TABLE links
(
    url VARCHAR(255) NOT NULL,
    last_visited TIMESTAMP
)

这将允许我一遍又一遍地添加相同的URL,除非我写了一些类似于上面的PHP代码来阻止这种情况的发生。

但是,我的定义是要改变的

CREATE TABLE links
(
  url VARCHAR(255)  NOT NULL,
  last_visited TIMESTAMP,
  PRIMARY KEY (url)
)

然后,当我试图插入相同的值两次,这将使mysql抛出一个错误。

在PHP中的例子是

$result = mysql_query("INSERT INTO links (url, last_visited) VALUES ('http://www.example.com/', NOW()", $conn);

if (!$result)
{
    die('Could not Insert Row 1');
}

$result2 = mysql_query("INSERT INTO links (url, last_visited) VALUES ('http://www.example.com/', NOW()", $conn);

if (!$result2)
{
    die('Could not Insert Row 2');
}

如果你运行这个,你会发现在第一次尝试时,脚本会死掉,注释Could not Insert Row 2 。 但是,在随后的运行中,它会死于Could not Insert Row 1

这是因为MySQL知道url是表的键。 主键是该行的唯一标识符。 大多数情况下,将行的唯一标识符设置为数字很有用。 这是因为MySQL查找数字比查找文本更快。 在MySQL中,键(特别是主键)用于定义两个表之间的关系。 例如,如果我们有一个用户表,我们可以定义为

CREATE TABLE users (
  username VARCHAR(255)  NOT NULL,
  password VARCHAR(40) NOT NULL,
  PRIMARY KEY (username)
)

但是,当我们想要存储用户所做的信息时,我们必须将用户名与该信息一起存储,以确定该信息属于该用户。

我已经提到MySQL查询数字比字符串更快,所以这意味着当我们不需要的时候,我们会花时间查找字符串。

为了解决这个问题,我们可以添加一个额外的列user_id,并将其设置为主键(所以当根据文章查找用户记录时,我们可以更快地找到它)

CREATE TABLE users (
  user_id INT(10)  NOT NULL AUTO_INCREMENT,
  username VARCHAR(255)  NOT NULL,
  password VARCHAR(40)  NOT NULL,
  PRIMARY KEY (`user_id`)
)

你会注意到我还在这里添加了一些新东西 - AUTO_INCREMENT。 这基本上允许我们让这个领域照顾自己。 每插入一个新行,它都会将前一个数字加1,并将其存储起来,所以我们不必担心编号,只能让它自己做。

所以,通过上面的表格,我们可以做类似的事情

INSERT INTO users (username, password) VALUES('Mez', 'd3571ce95af4dc281f142add33384abc5e574671');

接着

INSERT INTO users (username, password) VALUES('User', '988881adc9fc3655077dc2d4d757d480b5ea0e11');

当我们从数据库中选择记录时,我们得到以下内容:

mysql> SELECT * FROM users;
+---------+----------+------------------------------------------+
| user_id | username | password                                 |
+---------+----------+------------------------------------------+
|       1 | Mez      | d3571ce95af4dc281f142add33384abc5e574671 |
|       2 | User     | 988881adc9fc3655077dc2d4d757d480b5ea0e11 |
+---------+----------+------------------------------------------+
2 rows in set (0.00 sec)

但是,在这里 - 我们有一个问题 - 我们仍然可以添加具有相同用户名的其他用户! 显然,这是我们不想做的事情!

mysql> SELECT * FROM users;
+---------+----------+------------------------------------------+
| user_id | username | password                                 |
+---------+----------+------------------------------------------+
|       1 | Mez      | d3571ce95af4dc281f142add33384abc5e574671 |
|       2 | User     | 988881adc9fc3655077dc2d4d757d480b5ea0e11 |
|       3 | Mez      | d3571ce95af4dc281f142add33384abc5e574671 |
+---------+----------+------------------------------------------+
3 rows in set (0.00 sec)

让我们改变我们的表格定义!

CREATE TABLE users (
  user_id INT(10)  NOT NULL AUTO_INCREMENT,
  username VARCHAR(255)  NOT NULL,
  password VARCHAR(40)  NOT NULL,
  PRIMARY KEY (user_id),
  UNIQUE KEY (username)
)

让我们看看当我们现在尝试插入同一个用户两次会发生什么。

mysql> INSERT INTO users (username, password) VALUES('Mez', 'd3571ce95af4dc281f142add33384abc5e574671');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO users (username, password) VALUES('Mez', 'd3571ce95af4dc281f142add33384abc5e574671');
ERROR 1062 (23000): Duplicate entry 'Mez' for key 'username'

好哇! 我们现在尝试第二次插入用户名时会出现错误。 使用类似上面的东西,我们可以在PHP中检测到这一点。

现在,让我们回到我们的链接表,但有一个新的定义。

CREATE TABLE links
(
    link_id INT(10)  NOT NULL AUTO_INCREMENT,
    url VARCHAR(255)  NOT NULL,
    last_visited TIMESTAMP,
    PRIMARY KEY (link_id),
    UNIQUE KEY (url)
)

让我们在数据库中插入“http://www.example.com”。

INSERT INTO links (url, last_visited) VALUES ('http://www.example.com/', NOW());

如果我们尝试再次插入....

ERROR 1062 (23000): Duplicate entry 'http://www.example.com/' for key 'url'

但是,如果我们想更新上次访问的时间,会发生什么?

那么,我们可以做一些复杂的PHP,如下所示:

$result = mysql_query("SELECT * FROM links WHERE url = 'http://www.example.com/'", $conn);

if (!$result)
{
    die('There was a problem executing the query');
}

$number_of_rows = mysql_num_rows($result);

if ($number_of_rows > 0)
{
    $result = mysql_query("UPDATE links SET last_visited = NOW() WHERE url = 'http://www.example.com/'", $conn);

    if (!$result)
    {
        die('There was a problem updating the links table');
    }
}

或者,甚至可以获取数据库中的行的id并使用它来更新它。

$ result = mysql_query(“SELECT * FROM links WHERE url ='http://www.example.com/'”,$ conn);

if (!$result)
{
    die('There was a problem executing the query');
}

$number_of_rows = mysql_num_rows($result);

if ($number_of_rows > 0)
{
    $row = mysql_fetch_assoc($result);

    $result = mysql_query('UPDATE links SET last_visited = NOW() WHERE link_id = ' . intval($row['link_id'], $conn);

    if (!$result)
    {
        die('There was a problem updating the links table');
    }
}

但是,MySQL有一个很好的内置功能,叫做REPLACE INTO

让我们看看它是如何工作的。

mysql> SELECT * FROM links;
+---------+-------------------------+---------------------+
| link_id | url                     | last_visited        |
+---------+-------------------------+---------------------+
|       1 | http://www.example.com/ | 2011-08-19 23:48:03 |
+---------+-------------------------+---------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO links (url, last_visited) VALUES ('http://www.example.com/', NOW());
ERROR 1062 (23000): Duplicate entry 'http://www.example.com/' for key 'url'
mysql> REPLACE INTO links (url, last_visited) VALUES ('http://www.example.com/', NOW());
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM links;
+---------+-------------------------+---------------------+
| link_id | url                     | last_visited        |
+---------+-------------------------+---------------------+
|       2 | http://www.example.com/ | 2011-08-19 23:55:55 |
+---------+-------------------------+---------------------+
1 row in set (0.00 sec)

请注意,使用REPLACE INTO ,它会更新last_visited时间,而不会引发错误!

这是因为MySQL检测到你正在尝试替换一行。 它知道你想要的行,因为你已经设置url是唯一的。 MySQL通过使用你传入的位应该是唯一的(在这种情况下是url)并且为该行更新其他值来指出要替换的行。 它也更新了link_id - 这有点意外! (事实上​​,直到我看到它发生,我才意识到这会发生!)

但是如果你想添加一个新的URL呢? 那么,如果找不到匹配的唯一行, REPLACE INTO将会愉快地插入一个新行!

mysql> REPLACE INTO links (url, last_visited) VALUES ('http://www..com/', NOW());
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM links;
+---------+-------------------------------+---------------------+
| link_id | url                           | last_visited        |
+---------+-------------------------------+---------------------+
|       2 | http://www.example.com/       | 2011-08-20 00:00:07 |
|       3 | http://www..com/ | 2011-08-20 00:01:22 |
+---------+-------------------------------+---------------------+
2 rows in set (0.00 sec)

我希望这可以回答你的问题,并给你更多关于MySQL如何工作的信息!


首先,准备数据库

  • 域名不区分大小写,但是您必须假定URL的其余部分是。 (并不是所有的网络服务器都要遵守URL中的大小写,但是大多数都是这样做的,而且你不能通过查看来简单地分辨)
  • 假设您需要存储多个域名,请使用区分大小写的排序规则。
  • 如果您决定将URL存储在两列中(一个用于域名,一个用于资源定位器),请考虑对域名使用不区分大小写的排序规则以及资源定位器的区分大小写的排序规则。 如果我是你,我会测试两种方式(一列中的URL与两列中的URL)。
  • 在URL列上放置一个UNIQUE约束。 或者在一对列上,如果将域名和资源定位符存储在单独的列中,则为UNIQUE (url, resource_locator)
  • 使用CHECK()约束将编码的URL保留在数据库之外。 这个CHECK()约束对于防止不良数据通过批量复制或通过SQL外壳进入非常重要。

其次,准备网址

第三 ,如果你只插入URL, 不要先测试它的存在 。 相反,如果值已经存在,则尝试插入并捕获将会得到的错误。 每测试一次新的URL,测试和插入命中数据库两次。 插入和陷阱只是一次访问数据库。 请注意,insert-and-trap与insert-and-ignore-errors不是一回事。 只有一个特定的错误意味着你违反了独特的约束; 其他错误意味着还有其他问题。

另一方面,如果您要将URL和其他一些数据一起插入同一行,则需要提前确定是否要通过以下方式处理重复的URL

REPLACE消除了重复键重要错误的必要性,但如果有外键引用,它可能会有不幸的副作用。


在考虑解决这个问题时,你需要先定义一个“重复的URL”对你的项目意味着什么。 这将决定如何在将URL添加到数据库之前对URL进行规范化

至少有两个定义:

  1. 如果两个URL代表相同的资源,则认为是重复的,对相应的生成相应内容的Web服务一无所知。 一些考虑包括:
  2. 应用基本的URL规范化(例如小写方案和域名,提供默认端口,通过参数名称稳定排序查询参数,在HTTP和HTTPS的情况下删除哈希部分,...), 考虑网络服务。 也许你会认为所有的Web服务都足够聪明,可以对Unicode输入进行规范(比如维基百科),所以你可以应用Unicode规范化表单规范组合(NFC)。 你会去除www. '从所有堆栈溢出URL。 您可以使用PostRank的postrank-uri代码(移植到PHP)来移除各种不必要的URL(例如&utm_source=... )。

定义1导致一个稳定的解决方案(即没有进一步的规范化可以执行,并且URL的规范化不会改变)。 定义2,我认为是人们认为URL规范化的定义,导致规范化例程,可以在不同的时刻产生不同的结果。

无论您选择哪种定义,我都建议您为方案,登录,主机,端口和路径部分使用单独的列。 这将允许您智能地使用索引。 方案和主机的列可以使用字符整理(所有的字符整理在MySQL中不区分大小写),但登录和路径的列需要使用二进制,不区分大小写的整理。 另外,如果使用定义2,则需要保留原始方案,权限和路径部分,因为某些规范化规则可能会不时地添加或删除。

编辑:这里是示例表定义:

CREATE TABLE `urls1` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `scheme` VARCHAR(20) NOT NULL,
    `canonical_login` VARCHAR(100) DEFAULT NULL COLLATE 'utf8mb4_bin',
    `canonical_host` VARCHAR(100) NOT NULL COLLATE 'utf8mb4_unicode_ci', /* the "ci" stands for case-insensitive. Also, we want 'utf8mb4_unicode_ci'
rather than 'utf8mb4_general_ci' because 'utf8mb4_general_ci' treats accented characters as equivalent. */
    `port` INT UNSIGNED,
    `canonical_path` VARCHAR(4096) NOT NULL COLLATE 'utf8mb4_bin',

    PRIMARY KEY (`id`),
    INDEX (`canonical_host`(10), `scheme`)
) ENGINE = 'InnoDB';


CREATE TABLE `urls2` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `canonical_scheme` VARCHAR(20) NOT NULL,
    `canonical_login` VARCHAR(100) DEFAULT NULL COLLATE 'utf8mb4_bin',
    `canonical_host` VARCHAR(100) NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `port` INT UNSIGNED,
    `canonical_path` VARCHAR(4096) NOT NULL COLLATE 'utf8mb4_bin',

    `orig_scheme` VARCHAR(20) NOT NULL, 
    `orig_login` VARCHAR(100) DEFAULT NULL COLLATE 'utf8mb4_bin',
    `orig_host` VARCHAR(100) NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `orig_path` VARCHAR(4096) NOT NULL COLLATE 'utf8mb4_bin',

    PRIMARY KEY (`id`),
    INDEX (`canonical_host`(10), `canonical_scheme`),
    INDEX (`orig_host`(10), `orig_scheme`)
) ENGINE = 'InnoDB';

表'urls1'用于根据定义1存储规范URL。表'urls2'用于根据定义2存储规范URL。

不幸的是,你不能在元组(`scheme` /`canonical_scheme`,`canonical_login`,`canonical_host`,`port`,`canonical_path`)上指定UNIQUE约束,因为MySQL将InnoDB密钥的长度限制为767字节。


简单的SQL解决方案需要一个独特的领域; 逻辑解决方案不。

你应该规范你的网站,以确保没有重复。 PHP中的函数,如strtolower()urldecode()rawurldecode()

假设:您的表名称是“网站”,您的网址的列名称是“网址”,与网址关联的任意数据位于“数据”列中。

逻辑解决方案

SELECT COUNT(*) AS UrlResults FROM websites WHERE url='http://www.domain.com'

在继续使用INSERT语句之前,使用SQL或PHP中的if语句测试以前的查询,以确保它为0。

简单的SQL语句

场景1:你的数据库是一个先到先得的表,你不希望将来有重复的条目。

ALTER TABLE websites ADD UNIQUE (url)

这将防止任何条目能够被输入到数据库中,如果URL值已经存在于该列中。

场景2:您需要每个网址的最新信息,并且不希望重复内容。 这种情况有两种解决方案。 (这些解决方案也需要'网址'是唯一的,所以方案1中的解决方案也需要执行。)

REPLACE INTO websites (url, data) VALUES ('http://www.domain.com', 'random data')

这将触发DELETE操作,如果在所有情况下行存在,然后是INSERT,那么请小心ON DELETE声明。

INSERT INTO websites (url, data) VALUES ('http://www.domain.com', 'random data')
ON DUPLICATE KEY UPDATE data='random data'

如果行存在,将触发UPDATE操作,如果不存在则触发INSERT。







mysql