0

I use this SQL query below for my .net licensing system, that licenses users through a back-end api written in PHP.

Now i'm not too smart when it comes to SQL, but if someone could point me in the right direction to optimize the following code, it would greatly help!

CONNECTION PHP:

<?php
define('DB_HOST', 'localhost');
define('DB_NAME', 'test');
define('DB_USERNAME', 'user');
define('DB_PASSWORD', 'Codwawmw2%');

$odb = new PDO('mysql:host=' . DB_HOST . ';dbname=' . DB_NAME, DB_USERNAME, DB_PASSWORD);
?>

SQL QUERY:

} elseif ($Action == "BackLogin"){
    $userID123 = $_GET['uid'];
    $hwid = $_GET['hwid'];
    $ip = getRealIpAddr();
    $pcname = $_GET['pcname'];
    $time123 = time();

    $GetRows = $odb->query("SELECT * FROM `Account` WHERE uid = '$userID123'");
    $row_count = $GetRows->rowCount();
    $count = $row_count;
    if($count == 0){
        $odb->exec("INSERT INTO `Account` (id, uid, cpukey, ip, pcname, online, function, lastlogon) VALUES(NULL,'$userID123','$hwid','$ip','$pcname','0','None','$time123')");
        $response = 'Not Banned';
    } else {
        $response = 'Not Banned';
    }

    if ($response == "Not Banned")
    {
        $finaleresponse = "Success";
        $odb->exec("UPDATE Account SET `online` = '1', `lastlogon` = '$time123', `ip` = '$ip' WHERE `uid` = '$userID123'");
    } 
    else
    {
        $finaleresponse = "Error";
    }
    echo Encrypt($finaleresponse);
}

By the way, the reason I need to optimize the script is that every time its ran by 50+ clients simultaneously it will cause heavy load on the website/server and will literally lag the whole site to the point of no load...

Thanks to anyone who can help :)

TymeBomb
  • 117
  • 2
  • 12
  • 5
    [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 Mar 07 '17 at 22:20
  • 4
    Optimize in what way? Speed? Efficiency? Lines of code? – Jay Blanchard Mar 07 '17 at 22:20
  • I have added at the bottom what it needs to be optimized for. – TymeBomb Mar 07 '17 at 22:21
  • 1
    You don't seem to have an AI'd column or PK. AI'd columns don't accept NULL values, that's why I say this. – Funk Forty Niner Mar 07 '17 at 22:23
  • 1
    Are your tables indexed? 50 users is *nothing* where MySQL is concerned. – Jay Blanchard Mar 07 '17 at 22:23
  • 1
    **WARNING**: When using PDO you should be using [prepared statements](http://php.net/manual/en/pdo.prepared-statements.php) with placeholder values and supply any user data only as arguments on `execute` . In this code you have potentially severe [SQL injection bugs](http://bobby-tables.com/). Refer to [PHP The Right Way](http://www.phptherightway.com/) for advice on how to avoid problem like this. – tadman Mar 07 '17 at 22:25
  • You should also use a connection pool instead of opening a connection per-request, a habit that's highly wasteful. – tadman Mar 07 '17 at 22:25
  • Will take all your comments into consideration. I will try prepared statements and same with connection pools as I could see that working :) Thanks will report back if I have problems. – TymeBomb Mar 07 '17 at 22:29

1 Answers1

0

Please get the selected fields instead of *. to get your result fast.

$GetRows = $odb->query("SELECT uid, cpukey, ip, pcname FROM Account WHERE uid = '$userID123'");

Murari Pradip Singh
  • 39
  • 5