I need a help to do a mysql query

Question

I have these three tables below in DB

Tables

`accounts` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(32) NOT NULL DEFAULT '',
      `password` varchar(255) NOT NULL,
      `salt` varchar(40) NOT NULL DEFAULT '',
      `premdays` int(11) NOT NULL DEFAULT '0',
      `lastday` int(10) unsigned NOT NULL DEFAULT '0',
      `email` varchar(255) NOT NULL DEFAULT '',
      `key` varchar(32) NOT NULL DEFAULT '0',
      `blocked` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'internal usage',
      `warnings` int(11) NOT NULL DEFAULT '0',
      `group_id` int(11) NOT NULL DEFAULT '1',
      `page_access` int(11) DEFAULT NULL,
      `page_lastday` int(11) DEFAULT NULL,
      `email_new` varchar(255) DEFAULT NULL,
      `email_new_time` int(15) DEFAULT NULL,
      `rlname` varchar(255) DEFAULT NULL,
      `location` varchar(255) DEFAULT NULL,
      `created` int(16) DEFAULT NULL,
      `email_code` varchar(255) DEFAULT NULL,
      `next_email` int(11) DEFAULT NULL,
      `premium_points` int(11) DEFAULT NULL,
      `nickname` char(48) DEFAULT NULL,
      `avatar` char(48) DEFAULT NULL,
      `about_me` text,
      `vip_time` int(15) NOT NULL,
      `event_points` int(11) NOT NULL DEFAULT '0',
      PRIMARY KEY (`id`),
      UNIQUE KEY `name` (`name`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

`players` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `group_id` int(11) NOT NULL DEFAULT '1',
  `account_id` int(11) NOT NULL DEFAULT '0',
  `online` tinyint(1) NOT NULL DEFAULT '0',
  `deleted` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`,`deleted`),
  KEY `account_id` (`account_id`),
  KEY `group_id` (`group_id`),
  KEY `online` (`online`),
  KEY `deleted` (`deleted`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

`gamecodes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `gamecode` varchar(50) NOT NULL,
  `accountname` varchar(50) NOT NULL,
  `premium_points` int(11) NOT NULL,
  `alreadyused` varchar(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ;

This is my .PHP that insert FORM's information into TABLE GAME CODES

<?php
        function anti_injection($sql)
        {
            $sql = preg_replace(sql_regcase("/(from|select|insert|delete|where|drop table|show tables|#|\*|--|\\\\)/"),"",$sql);
            $sql = trim($sql);
            $sql = strip_tags($sql);
            $sql = addslashes($sql);
            return $sql;
        }

        $accountorname = anti_injection($_POST['accountorname']);
        $gamecode = $_POST['gamecode'];
        $category = $_POST['category'];
        $premiumpoints = anti_injection($_POST['premiumpoints']);

        switch ($category) {
            case 'accountname':
                $insertquery = "INSERT INTO gamecodes (gamecode, accountname, premium_points, alreadyused) VALUES ('$gamecode','$accountorname',$premiumpoints,'N')";
                break;
            case 'charactername':
                $insertquery = "INSERT INTO gamecodes (gamecode, accountname, premium_points, alreadyused) 
                        SELECT '$gamecode',accounts.name,$premiumpoints,'N' 
                        FROM accounts 
                        JOIN players
                        ON accounts.id = players.account_id 
                        WHERE players.name = '$accountorname'";
                break;
        }

        $result = mysql_query($insertquery);
    ?>

The problems are:

in case 'accountname':, before INSERT it must check if informed account is valid in table ACCOUNTS.
in case 'charactername':, before INSERT it must check if informed character name is valid in table PLAYERS

I could not do, can someone help me?

*Obligatory:* The `mysql_*` functions will be [deprecated in PHP 5.5](http://php.net/manual/en/faq.databases.php#faq.databases.mysql.deprecated). It is not recommended for writing new code as it will be removed in the future. Instead, either the [MySQLi](http://php.net/manual/en/book.mysqli.php) or [PDO](http://php.net/manual/en/book.pdo.php) and [be a better PHP Developer](http://jason.pureconcepts.net/2012/08/better-php-developer/). — Jason McCreary, Jun 19 '13 at 12:09
I'd suggest replacing your `anti_injection` function with [`mysqli::real_escape_string`](http://www.php.net/manual/en/mysqli.real-escape-string.php). — jterry, Jun 19 '13 at 12:22
That `anti_injection` is pretty LOLworthy, just use PDO PLEAAAASE — 000, Jun 19 '13 at 12:38

score 1 · Accepted Answer · answered Jun 19 '13 at 12:13

1

$selectquery = "SELECT * from accounts where accountname='$accountname'";
$selectresult = mysql_query($selectquery);
if (mysql_num_rows($selectresult)) {
    // account exists, now you can do the INSERT
}

NOTICE!

The mysql extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used.

answered Jun 19 '13 at 12:13

000

26,951
10
71
101

Do you have a tutorial? – user2498466 Jun 19 '13 at 12:54
http://php.net/manual/en/book.pdo.php and http://stackoverflow.com/questions/15990857/reference-frequently-asked-questions-about-pdo – 000 Jun 19 '13 at 12:55

score 1 · Answer 2 · answered Jun 19 '13 at 12:19

You can use a function for that purpose like this one:

function Validate($table, $name)
{
    if ($res = mysql_query("SELECT Count(*) as isvalid FROM $table WHERE name = '$name'"))
    {
        $isvalid = 0;
        @extract(mysql_fetch_assoc($res));
        if ($isvalid == 0) return false;
        else return true;
    }
}

So you call this way:

case 'accountname':
    if (Validate("accounts", $accountorname)) { 
        // Do things
    }
case 'charactername':
    if (Validate("players", $accountorname)) { 
        // Do things
    }

I didn't tested it and I confess that is not the best approach, but it should do what you want.

By the way, your SQL injection functions have some breach holes. You have to filter all your inputs cause user can alter the input data of any control in the browser. Consider reviewing that.

score 0 · Answer 3 · answered Jun 19 '13 at 12:19

You could always do another query to check before you do this. With this you'll still run the risk of someone deleting the account in between the queries. If that's a concern for you, you should look into transactions.

    $shouldinsert = false;
    switch ($category) {
        case 'accountname':
            $result = mysql_query("SELECT count(*) as account_count FROM accounts WHERE name = '$accountorname'");
            $shouldinsert = mysql_num_rows($result) > 0;
            $insertquery = "INSERT INTO gamecodes (gamecode, accountname, premium_points, alreadyused) VALUES ('$gamecode','$accountorname',$premiumpoints,'N')";
            break;
        case 'charactername':
            //I'm not quite sure how to check if a charactername is 'valid' but this should get you started
            $result = mysql_query("SELECT count(*) as account_count FROM players WHERE name = '$charactername'");
            $shouldinsert = mysql_num_rows($result) > 0;
            $insertquery = "INSERT INTO gamecodes (gamecode, accountname, premium_points, alreadyused) 
                    SELECT '$gamecode',accounts.name,$premiumpoints,'N' 
                    FROM accounts 
                    JOIN players
                    ON accounts.id = players.account_id 
                    WHERE players.name = '$accountorname'";
            break;
    }
    if($shouldinsert) {
        mysql_query($insertquery);
    }

I need a help to do a mysql query

3 Answers3