3D spaceshooter with online scoreboard, online demos, ship building. Now entirely defunct, but might be resurrected

class.DbUtil.php 9.9KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391
  1. <?php
  2. class DbUtil{
  3. public static function registerNewUser($name, $password, $email = "", $country = ""){
  4. $name = mysql_real_escape_string($name);
  5. $password = mysql_real_escape_string($password);
  6. $country = mysql_real_escape_string($country);
  7. $email = mysql_real_escape_string($email);
  8. $result = self::query("SELECT COUNT(*) FROM `SECTOR_USERS` WHERE `name` = '$name';");
  9. $rows=0;
  10. list($rows) = mysql_fetch_row($result);
  11. if($rows != 0){
  12. XmlGen::error_exit("REGISTRATION_FAILED","Entered name is already taken.");
  13. }
  14. $uid = "";
  15. while(true){
  16. $uid = "U-".Util::uniqueString(12);
  17. $result = self::query("SELECT COUNT(*) FROM `SECTOR_USERS` WHERE `uid` = '$uid' AND `removed` = '0';");
  18. $row = mysql_fetch_row($result);
  19. if($row[0] == 0) break;
  20. }
  21. $time = time();
  22. self::query("
  23. INSERT
  24. INTO `SECTOR_USERS`
  25. (`uid`,`name`,`password`,`email`,`reg_time`,`country`)
  26. VALUES
  27. ('$uid','$name','$password','$email','$time','$country');
  28. ");
  29. self::refreshLogin($uid);
  30. exit();
  31. }
  32. /*
  33. $_REQUEST["uid"],
  34. $_REQUEST["name"],
  35. $_REQUEST["password"],
  36. $_REQUEST["email"],
  37. $_REQUEST["country"]
  38. */
  39. public static function deleteProfile($uid){
  40. $u = mysql_real_escape_string($uid);
  41. self::query("UPDATE `SECTOR_USERS` SET `removed`='1' WHERE `uid` = '$u' LIMIT 1;");
  42. self::query("UPDATE `SECTOR_SCORES` SET `removed`='1' WHERE `uid` = '$u';");
  43. // self::query("DELETE FROM `SECTOR_USERS` WHERE `uid` = '$u' LIMIT 1;");
  44. // self::query("DELETE FROM `SECTOR_SCORES` WHERE `uid` = '$u';");
  45. echo XmlGen::deleteMessage();
  46. exit();
  47. }
  48. public static function modifyProfile($uid,$name,$password,$email,$country){
  49. if($name == null && $password == null && $email == null && $country == null){
  50. XmlGen::error_exit("INCOMPLETE_COMMAND","Nothing to change.");
  51. }
  52. $n = mysql_real_escape_string($name);
  53. $u = mysql_real_escape_string($uid);
  54. $result = self::query("SELECT COUNT(*) FROM `SECTOR_USERS` WHERE `name` = '$n' AND `uid` != '$u';");
  55. $rows=0;
  56. list($rows) = mysql_fetch_row($result);
  57. if($rows != 0){
  58. XmlGen::error_exit("NAME_NOT_UNIQUE");
  59. }
  60. $sql = "";
  61. if($name != null){
  62. $sql .= ",`name` = '".mysql_real_escape_string($name)."'";
  63. }
  64. if($password != null){
  65. $sql .= ",`password` = '".mysql_real_escape_string($password)."'";
  66. }
  67. if($email == null) $email = "";
  68. $sql .= ",`email` = '".mysql_real_escape_string($email)."'";
  69. if($country == null) $country = "";
  70. $sql .= ",`country` = '".mysql_real_escape_string($country)."'";
  71. $sql = substr($sql,1);
  72. self::query("
  73. UPDATE `SECTOR_USERS`
  74. SET $sql
  75. WHERE `uid` = '$uid'
  76. LIMIT 1;
  77. ");
  78. self::refreshLogin($uid);
  79. exit();
  80. }
  81. public static function logIn($name, $passwordHash){
  82. $name = mysql_real_escape_string(trim($name));
  83. $result = self::query("SELECT `uid`,`password` FROM `SECTOR_USERS` WHERE `name` = '$name' AND `removed` = '0';");
  84. if(mysql_num_rows($result) == 0){
  85. XmlGen::error_exit("LOGIN_FAILED","Bad name or password.");
  86. }
  87. $row = mysql_fetch_assoc($result);
  88. $dbPwd = $row['password'];
  89. $uid = $row['uid'];
  90. // double hash with ugly salt!
  91. if( Util::calcSecureHash($name,$dbPwd) != $passwordHash){
  92. XmlGen::error_exit("LOGIN_FAILED","Bad name or password.");
  93. }
  94. self::refreshLogin($uid);
  95. exit();
  96. }
  97. public static function isTokenValid($uid, $token){
  98. $uid = mysql_real_escape_string($uid);
  99. $result = self::query("SELECT `auth_token` FROM `SECTOR_USERS` WHERE `uid` = '$uid' AND `removed` = '0';");
  100. if(mysql_num_rows($result) == 0){
  101. return false; // bad UID
  102. }
  103. $row = mysql_fetch_array($result);
  104. return $row[0] == $token;
  105. }
  106. public static function getInfo(){
  107. $result = self::query("SELECT * FROM `SECTOR_INFO`;");
  108. $entries = array();
  109. while($row = mysql_fetch_row($result)){
  110. $entries[$row[0]] = $row[1];
  111. }
  112. $version = $entries['VERSION_NUMBER']+0;
  113. if($_REQUEST["VERSION"]<=$version){
  114. // only publicly available releases are counted,
  115. // not prepared ones with higher version number
  116. // add to counter.
  117. $midnight = strtotime('midnight');
  118. $result = self::query("SELECT COUNT(*) FROM `SECTOR_COUNTER` WHERE `date` = '$midnight';");
  119. $row = mysql_fetch_array($result);
  120. if($row[0] == 0){
  121. self::query("INSERT INTO `SECTOR_COUNTER`(`date`,`visits`) VALUES ('$midnight','1');");
  122. }else{
  123. self::query("UPDATE `SECTOR_COUNTER` SET `visits`=`visits`+1 WHERE `date` = '$midnight' LIMIT 1;");
  124. }
  125. }
  126. echo XmlGen::infoTable($entries);
  127. exit();
  128. }
  129. public static function getUsers(){
  130. $result = self::query("SELECT `name`,`reg_time`,`country` FROM `SECTOR_USERS` WHERE `removed` = '0';");
  131. $entries = array();
  132. while($row = mysql_fetch_array($result)){
  133. $entries[] = $row;
  134. }
  135. echo XmlGen::userList($entries);
  136. exit();
  137. }
  138. public static function getLevels(){
  139. $result = self::query("SELECT `value` FROM `SECTOR_INFO` WHERE `key` = 'LEVELS_PATH';");
  140. $row = mysql_fetch_array($result);
  141. $path = $row[0];
  142. $result = self::query("SELECT `lid`,`title`,`filename`,`checksum`,`time` FROM `SECTOR_LEVELS` WHERE `removed` = '0';");
  143. $entries = array();
  144. while($row = mysql_fetch_array($result)){
  145. $row[2] = $path.$row[2];
  146. $entries[] = $row;
  147. }
  148. echo XmlGen::levelList($entries);
  149. exit();
  150. }
  151. public static function getLevelScores($lid, $changeFlag = null, $lastRecord = null){
  152. $lid = mysql_real_escape_string($lid);
  153. $result = self::query("SELECT COUNT(*) FROM `SECTOR_LEVELS` WHERE `lid` = '$lid' AND `removed` = '0';");
  154. $row = mysql_fetch_array($result);
  155. $cnt = $row[0];
  156. if($cnt==0) XmlGen::error_exit("NO_SUCH_LEVEL","No level with matching ID was found.");
  157. $result = self::query("
  158. SELECT
  159. `SECTOR_USERS`.`name` AS `username`,
  160. `SECTOR_SCORES`.`uid`,
  161. `SECTOR_SCORES`.`time`,
  162. `SECTOR_SCORES`.`score`
  163. FROM `SECTOR_SCORES` JOIN `SECTOR_USERS`
  164. WHERE
  165. (`SECTOR_SCORES`.`uid` = `SECTOR_USERS`.`uid`)
  166. AND (`lid`='$lid')
  167. AND (`SECTOR_SCORES`.`removed` = '0')
  168. ORDER BY `score` DESC, `time` DESC;
  169. ");
  170. // username, uid, time, score
  171. $entries = array();
  172. while($row = mysql_fetch_array($result)){
  173. $entries[] = $row;
  174. }
  175. echo XmlGen::scoreList($lid, $entries, $changeFlag, $lastRecord);
  176. exit();
  177. }
  178. public static function submitScore($uid, $lid, $score){
  179. $lid = mysql_real_escape_string($lid);
  180. $uid = mysql_real_escape_string($uid);
  181. $score = $score+0;
  182. $result = self::query("SELECT COUNT(*) FROM `SECTOR_LEVELS` WHERE `lid` = '$lid' AND `removed` = '0';");
  183. $row = mysql_fetch_array($result);
  184. $cnt = $row[0];
  185. if($cnt==0) XmlGen::error_exit("NO_SUCH_LEVEL","No level with matching ID was found.");
  186. $result = self::query("SELECT COUNT(*) FROM `SECTOR_SCORES` WHERE `lid` = '$lid' AND `uid` = '$uid';");
  187. $row = mysql_fetch_array($result);
  188. $cnt = $row[0];
  189. $time = time();
  190. $change = "false";
  191. $lastRecord = "-1";
  192. if($cnt==0){
  193. // INSERT
  194. self::query("INSERT INTO `SECTOR_SCORES`(`uid`,`lid`,`time`,`score`) VALUES ('$uid','$lid','$time','$score');");
  195. $change = "true";
  196. }else{
  197. $result = self::query("SELECT `score` FROM `SECTOR_SCORES` WHERE `lid` = '$lid' AND `uid` = '$uid';");
  198. $row = mysql_fetch_array($result);
  199. $scoreOld = $row[0];
  200. $lastRecord = "$scoreOld";
  201. if($scoreOld > $score){
  202. }else{
  203. // UPDATE
  204. self::query("UPDATE `SECTOR_SCORES` SET `time`='$time', `score`='$score' WHERE `lid` = '$lid' AND `uid` = '$uid' LIMIT 1;");
  205. if($scoreOld != $score) $change = "true";
  206. }
  207. }
  208. self::getLevelScores($lid, $change, $lastRecord);
  209. exit();
  210. }
  211. public static function refreshLogin($uid){
  212. $token = Util::uniqueString(20);
  213. self::query("
  214. UPDATE `SECTOR_USERS`
  215. SET `auth_token` = '$token'
  216. WHERE `uid` = '$uid'
  217. LIMIT 1;
  218. ");
  219. $result = self::query("SELECT `name`,`email`,`reg_time`,`country` FROM `SECTOR_USERS` WHERE `uid` = '$uid';");
  220. $row = mysql_fetch_assoc($result);
  221. $name = $row["name"];
  222. $email = $row["email"];
  223. $reg_time = $row["reg_time"];
  224. $country = $row["country"];
  225. echo XmlGen::sessionInfo($uid, $token, $name, $email, $reg_time, $country);
  226. }
  227. public static function addLevel($title, $filename){
  228. $result = self::query("SELECT COUNT(*) FROM `SECTOR_LEVELS` WHERE `filename` = '".mysql_real_escape_string($filename)."';");
  229. $row = mysql_fetch_array($result);
  230. $cnt = $row[0];
  231. if($cnt>0) XmlGen::error_exit("LEVEL_ALREADY_ADDED");
  232. $result = self::query("SELECT COUNT(*) FROM `SECTOR_LEVELS` WHERE `title` = '".mysql_real_escape_string($title)."';");
  233. $row = mysql_fetch_array($result);
  234. $cnt = $row[0];
  235. if($cnt>0) XmlGen::error_exit("LEVEL_NAME_NOT_UNIQUE");
  236. $result = self::query("SELECT `value` FROM `SECTOR_INFO` WHERE `key` = 'LEVELS_PATH_RELATIVE_TO_SERVER';");
  237. $row = mysql_fetch_array($result);
  238. $path = $row[0];
  239. $result = self::query("SELECT `value` FROM `SECTOR_INFO` WHERE `key` = 'LEVELS_PATH';");
  240. $row = mysql_fetch_array($result);
  241. $apath = $row[0];
  242. $fpath = $path.$filename;
  243. if(!file_exists($fpath)){
  244. XmlGen::error_exit("FILE_NOT_FOUND","Level file does not exist: ".$fpath);
  245. }
  246. if(substr($filename,strlen($filename)-4) != ".xml"){
  247. XmlGen::error_exit("BAD_FILE_FORMAT", "Level file must be XML: ".$fpath);
  248. }
  249. // generate a LID
  250. $lid = "";
  251. while(true){
  252. $lid = "L-".Util::uniqueString(9);
  253. $result = self::query("SELECT COUNT(*) FROM `SECTOR_LEVELS` WHERE `lid` = '$lid';");
  254. $row = mysql_fetch_row($result);
  255. if($row[0] == 0) break;
  256. }
  257. $hash = md5_file($fpath);
  258. $title = mysql_real_escape_string($title);
  259. $filename = mysql_real_escape_string($filename);
  260. $time = time();
  261. self::query("
  262. INSERT
  263. INTO `SECTOR_LEVELS`
  264. (`lid`,`title`,`filename`,`checksum`,`time`)
  265. VALUES
  266. ('$lid','$title','$filename','$hash','$time');
  267. ");
  268. echo XmlGen::levelAddedInfo($lid, $title, $apath.$filename, $hash, $time);
  269. exit();
  270. }
  271. public static function query($q){
  272. $res = mysql_query($q) or die(XmlGen::error("INTERNAL_ERROR", "DbError: ".mysql_error()));
  273. return $res;
  274. }
  275. }