how to do exect match on mysql_query?

Question

How to do absolute MySQL query match. Since I seem getting collision if a user is trying to call a url or a file via short link domain.ltd/NGV which collides with domain.ltd/ngv forcing the fetch script to pull /NGV file not /ngv

Here is the code which does the MySQL selections also the htaccess bit is provided

 $tag = $_REQUEST['rid'];

 $q = mysql_query("SELECT * FROM `media` WHERE `qp_tag` = '".mysql_escape_string($tag)."' LIMIT 1");

 $r = mysql_fetch_row($q);

 if(!empty($r)) {

     $f = stripslashes($r['file']);
     $t = stripslashes($r['type']);

     $c = file_get_contents($f);

     $api_html = <<<API_HTML_VIEW
     $c
             API_HTML_VIEW;

     echo $api_html;

 } else {

     $api_html = <<<API_HTML_VIEW
     We are sorry but we cannot find requested resource :(
             API_HTML_VIEW;

     echo $api_html;

 }

.htaccess code bit

RewriteRule ^([a-zA-Z0-9-]+)/?$ api.php?rid=$1 [L,QSA]

and here is the last bit of code to generate the actual short links which also may be the problem since i am not sure whats kicking the thing back at the present moment

function qp_tag() {

     $file_tag = $_FILES['file']['name'];
     $file_uni = uniqid();
     $short = strtolower(substr(base64_encode(crc32($file_tag)), 0, 3));  

     return $short;

}

Edited: The system works now the only problem is that it lags allot on selecting file if its a file

 $f = $r['file'];
 $t = $r['type'];
 $s = $r['size'];
 $n = $r['name'];

 header("Pragma: public"); // required
 header("Expires: 0");
 header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
 header("Cache-Control: private",false); // required for certain browsers
 header("Content-Type: ".$t."");
 header("Content-Disposition:attachment;filename=".$n."");
 header("Content-Length: ".$s);
 ob_clean(); 
 flush();

 $fp = fopen($f, "r"); 
 while (!feof($fp))
     {
         echo fread($fp, 65536); 
         flush(); // this is essential for large downloads
     }  
 fclose($fp);

Ben Lee · Accepted Answer · 2011-11-28T09:34:28.263

1

If I understand you correctly, you want to use the BINARY operator for case-sensitive matching:

$q = mysql_query("SELECT * FROM `media` WHERE BINARY `qp_tag` = '".mysql_escape_string($tag)."' LIMIT 1");

This is the same as your query above except I've inserted the word BINARY in the comparison.

Note that it's possible you won't be able to take full advantage of the any indexes on the comparison column if you do this. That may not be an issue at all for you, but take it into consideration if your table has a lot of rows in it.

edited Nov 28 '11 at 09:34

answered Nov 28 '11 at 09:22

Ben Lee

52,489
13
125
145

I wish to do absolute math so it pulls correct file/url from mysql so lets say the url request is for file/url under qp_tag of: OGG it will get that record and NOT record for OgG, oGG, ect... since from what I am getting right now it does not matter if you use OGG or OgG it will still call the same file which is a not something I want – PT Desu Nov 28 '11 at 09:31
Yes, the code I supplied should do exact matches like that. If $tag is "OGG", using `BINARY` will only get you records with exactly qp_tag of "OGG". It will NOT get records where it is "OgG" or "oGG", etc... – Ben Lee Nov 28 '11 at 09:32
I am starting to wonder now may it be a problem in htaccess? But then again htaccess does not do much. So it is a problem in api of the actual selection based on exact tag – PT Desu Nov 28 '11 at 09:32
@PTDesu, try the update I just posted. I moved the `BINARY` operator to a different position in the query. – Ben Lee Nov 28 '11 at 09:34
Great mate :) it works now if the tag does not match it provides and error else it does its job – PT Desu Nov 28 '11 at 09:41

score 1 · Answer 2 · answered Nov 28 '11 at 09:37

1

To get an exact match, you can change the collation on the qp_tag column to be a case-sensitive collation. Those are the collations that end in _cs , like latin1_general_cs . The default collation is usually latin1_swedish_ci, which is case-insensitive.

answered Nov 28 '11 at 09:37

Michael Low

24,276
16
82
119

@PTDesu, if the collation is "utf8_general_ci" then you can probably change it to "utf8_general_cs" or "utf8_bin" to get case-sensitive string matching in all cases (assuming nothing else important relies on the collation). The `BINARY` operator casts the column for the one particular call only. – Ben Lee Nov 28 '11 at 09:46
@BenLee actually it works fine, it will fail if no match is found, the only problem that i hit now is the file selection takes supper long and hangs quite a bit. I will post the code if you don't mind looking over it and telling me where I am going wrong to hand the output of file to browser – PT Desu Nov 28 '11 at 09:59
If it's the query that takes long, it's probably because (as per the mentioned caveat) it's not using the pg_tag index. Change the collation to utf8_general_cs, as @mikel suggested, then you don't have to use `BINARY` for case-sensitive search and your index will still be usable. – Ben Lee Nov 28 '11 at 10:08

how to do exect match on mysql_query?

2 Answers2