1

I want to search a query in a MySQL table called "keys" based on a key, and then return other values for that query. (e.g. "id")

This is how my table looks like:

id              key                          customer
88633631        gNjp4CW6E/VfdBqli6zBLw==     Name1
41317488        bi74frT3LFGTRkvoW7B31Q==     Name2

..and this is how my PHP code looks like:

<?php
    require 'config.inc.php';
    /* Declare variables */
    $key = $_GET["key"];   
    $id = "";
    $customer = "";
    /* Connect to database and grab the keys */
    @mysql_connect($g_mysql_host,$g_mysql_usr,$g_mysql_pass)
    or die("Couldn't connect to database server");
    @mysql_selectdb($g_mysql_db)
    or die("Couldn't select database");
    $query = "SELECT * FROM `keys` WHERE `key` = $key";
    $result = mysql_query($query);
    if (!$result) exit("INVALID KEY");
    else {
        while ($row = mysql_fetch_array($result)) {
            echo $row['id'];
        }
    }
?>

But this does not work. I think that the problem is caused by the 2 equals at the end of the key, but I'm not sure. If I want to search the "id" and then print the "key" of the searched "id", it works.

I don't want to remove the 2 equals at the end, because they are related to AES128 padding.

Actually, the problem is not caused by the 2 equals at the end of the key. It is caused by the "+" character inside the string, but if I remove it I can't decrypt the AES128 encrypted text.

Mario Bălănică
  • 23
  • 4
  • `key` and `keys` are reserved words in MySQL and ***should not*** be used as table or column names. https://dev.mysql.com/doc/refman/5.5/en/keywords.html – Jay Blanchard Apr 28 '17 at 19:48
  • ***Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php).*** [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). – Jay Blanchard Apr 28 '17 at 19:50
  • [Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)***. Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! – Jay Blanchard Apr 28 '17 at 19:50
  • This script is just for personal use. It works even if the column is named "key" . I have renamed it to "lic_key" but the problem persists. This question is not duplicate, because I have a different problem. – Mario Bălănică Apr 28 '17 at 19:56

3 Answers3

1

There are several things wrong with the way you do this.

First, you don't quote your value: '$key'

$query = "SELECT * FROM `keys` WHERE `key` = '$key'";

Second, your code is wide open to SQL injection. Escape your value:

$key = mysql_real_escape_string($_GET["key"]);

The code above is the absolute minimum you need to do.
The next thing is that the mysql_ functions have been deprecated a long time and have been removed in PHP 7.

You'll need to switch to either mysqli_ or PDO. The sooner you switch, the better. Please read this question for further information: How can I prevent SQL injection in PHP?

Apart from all the problems above, you'll save yourself (and even more so others, if they ever have to read your code) some headache if you don't ever use keywords and reserved words as table or column names, as Jay Blanchard has noted.

Community
  • 1
  • 1
Imanuel
  • 3,596
  • 4
  • 24
  • 46
  • https://dev.mysql.com/doc/refman/5.5/en/keywords.html – Jay Blanchard Apr 28 '17 at 19:53
  • Yes, I've read your comment above. Is that really the problem? The columns are escape with `\``, so it should work despite being bad practice, shouldn't it? – Imanuel Apr 28 '17 at 19:55
  • It works, but if the key is wrong, "if (!$result) exit("INVALID KEY");" does absolutely nothing. The page is blank. And if the key contains "+" the page is blank too. I have to remove "+" to get it working, but this is not what I want, because the key can't be decrypted. – Mario Bălănică Apr 28 '17 at 19:59
  • More of a caution than anything else @Pharaoh – Jay Blanchard Apr 28 '17 at 19:59
  • This answer fixed one of my problems. More details about the other ones here: http://stackoverflow.com/questions/43697754/search-base64-string-in-mysql-table-using-php/43697936#43697936 . Thanks. – Mario Bălănică Apr 29 '17 at 16:28
0

That is happening because you didn't escaped the input it should be 

"SELECT * FROM `keys` WHERE `key` = '$key'"

but anyway this is not the best way to do that, you have to use prepared statement and wrapper such as PDO

Something like this

$stmt = $db->prepare('SELECT * FROM `keys` WHERE `key` = :key');
$stmt->bindValue(":key", $_GET["key"]);

if ($stmt->execute()) {
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo $row['id'];
    }
}
Moustafa Elkady
  • 670
  • 1
  • 7
  • 17
0

what about if you change the following line

$query = "SELECT * FROM `keys` WHERE `key` = $key";

to

$query = "SELECT * FROM `keys` WHERE `key` = '".$key."' ";
Obaidul Kader
  • 217
  • 1
  • 7
  • Let's not teach/propagate sloppy and dangerous coding practices. If you post an answer without prepared statements [you may want to consider this before posting](http://meta.stackoverflow.com/q/344703/). Additionally [a more valuable answer comes from showing the OP the right method](https://meta.stackoverflow.com/a/290789/1011527). – Jay Blanchard Apr 28 '17 at 19:52