使用prepare语句在数据库中搜索数据

恐惧

一个简短的问题:),我之所以这样写是因为有人说我的代码容易受到mysql注入的攻击,这是学习Web编程中准备好的语句的必要条件,以避免任何用户将恶意数据或语句放入数据库中。一个从数据库中搜索数据的搜索功能,如果您输入类似“ torres”的字符串,那么我将搜索torres,但是如果您仅输入“ tor”,它将不会搜索名称中包含“ tor”的数据。 。我在使用准备好的语句时不知道正确的格式,如果您有建议,我很乐意采用:)

    <?php
if (isset($_POST['search'])) {

    $box = $_POST['box'];
    $box = preg_replace("#[^0-9a-z]#i","",$box);
    $grade =$_POST['grade'];
    $section = $_POST['section'];
    $strand = $_POST['strand'];




      $sql = "SELECT * FROM student WHERE fname LIKE ? or lname LIKE ? or mname LIKE ? or grade = ? or track = ? or section = ?";
        $stmt = mysqli_stmt_init($conn);
        if (!mysqli_stmt_prepare($stmt, $sql)){
            echo "SQL FAILED";
        }
        else {
            //bind the parameter place holder
            mysqli_stmt_bind_param($stmt, "ssssss",$box, $box, $box, $grade, $strand, $section);
            mysqli_stmt_execute($stmt);
            $result = mysqli_stmt_get_result($stmt);

            while($row = mysqli_fetch_assoc($result))
        {
            echo "<tr>";
            echo "<td>".$row['lname']."</td>";
            echo "<td>".$row['fname']."</td>";
            echo "<td>".$row['mname']."</td>";
            echo "<td>".$row['grade']."</td>";
            echo "<td>".$row['track']."</td>";
            echo "<td>".$row['section']."</td>";
       echo "</tr>";
        }

        }
凤凰艺术

按照要求:

@ArtisticPhoenix我显然更喜欢国王的方式[复合全文索引]。这应该是显示示例/解释的主要答案。

首先创建一个包含所有三个字段的全文本索引(这在PHPmyAdmin中,使用图像进行解释要容易一些)

在此处输入图片说明

然后执行如下查询:

#PDO version SELECT * FROM `temp` WHERE MATCH(fname,mname,lname)AGAINST(:fullname IN BOOLEAN MODE)
#MySqli version SELECT * FROM `temp` WHERE MATCH(fname,mname,lname)AGAINST(? IN BOOLEAN MODE)
SELECT * FROM `temp` WHERE MATCH(fname,mname,lname)AGAINST('edward' IN BOOLEAN MODE)

在此处输入图片说明

看起来很简单,但有些事情需要全文了解Min char count,即3(我认为)小于未搜索的最小字符数。这可以更改,但是需要修复数据库并重新启动MySql。

停用词,例如andthe等等。这些也可以在my.cnf中进行配置。

标点符号被忽略。这对名字似乎没什么大不了,但可以想到带连字符的姓氏。

通常,我将单词min减少为2,并将停用词指向一个空文件(禁用它们)。

The match against syntax is quite different, it's pretty powerful but it's not really used outside of full text. An example is: this is the wild card * and you use '"' double quotes for exact phrase match '"match exactly"', and + is logical AND, such as word+ word+ (default is or), - is do not match this etc... If I remember right, I used it a bunch a few years ago but haven't had to use it recently.

For example doing "begins with" on a partial word

SELECT * FROM `temp` WHERE MATCH(fname,mname,lname)AGAINST('edwar*' IN BOOLEAN MODE)

Same result matches one row. The obvious benefit is searching all 3 fields at the same time, but the full text syntax itself can be quite useful too.

For more information:

https://dev.mysql.com/doc/refman/8.0/en/fulltext-boolean.html

PS. I might add that using OR in a query can really kill performance, I've went as far as to replace simple OR with a UNION because of how bad the performance is on a large table. Logically the DB optimizer has to rescan the entire table for an OR, unlike AND where it can use the result of the previous expression to reduce the next expressions data set (or that is how I understand it). I can say the performance difference is very noticeable using OR vs UNION.

This is true for a compound full text index vs doing OR on each field separately. By default fulltext is faster, but it's even faster this way.

To fix your current query (for the sake of completeness)

You need whats known as an exclusive or, like this:

SELECT * FROM student WHERE ( fname LIKE ? OR lname LIKE ? OR mname LIKE ? ) AND grade = ? AND track = ? AND section = ?

What this does is group the OR's together so that they evalute as one expression to the "next level up" ( outside the parenthesis ). Basically order of operations. In English, you would have to match at least 1 of these columns fname, lname, mname AND you would also have to match all of the rest of the columns as well, to get a result returned for any given row.

  • If you use all OR (as you are now) and any single field matches, then the query comes back as true with matches. Which is the behaviour you are experiencing now.

  • If you simply change everything outside of the name fields to AND, Basically remove the parenthesis

Like this:

 #this is wrong don't use it.
 SELECT * FROM student WHERE fname LIKE ? OR lname LIKE ? OR mname LIKE ? AND grade = ? AND track = ? AND section = ?

Then you have to match this way.

 (grade AND track AND section AND mname) OR lname OR fname 

因此,如果姓氏或名字匹配,则无论其他字段如何,您都可以得到结果。但是mname您将发现字段必须与所有其他字段匹配才能得到结果(但您不太可能会注意到这一点)。因为,似乎查询可以按您想要的方式工作,但仅当mname匹配时才有效

我希望这是有道理的。将WHERE子句视为同样的逻辑规则适用的IF条件可能会有所帮助。

干杯!

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章