fix a few issues with sql

4 files changed, 174 insertions, 52 deletions

diff --git a/.gitignore b/.gitignore
index f8a403c..3c12ed1 100644
--- a/.gitignore
+++ b/.gitignore
@@ -1,2 +1,3 @@
 /config.json
 /node_modules
+/package-lock.json
diff --git a/LICENSE b/LICENSE
index fccea72..670154e 100644
--- a/LICENSE
+++ b/LICENSE
@@ -1,15 +1,116 @@
-ISC License
+CC0 1.0 Universal
 
-Copyright (c) 2018, The Mana World
+Statement of Purpose
 
-Permission to use, copy, modify, and/or distribute this software for any
-purpose with or without fee is hereby granted, provided that the above
-copyright notice and this permission notice appear in all copies.
+The laws of most jurisdictions throughout the world automatically confer
+exclusive Copyright and Related Rights (defined below) upon the creator and
+subsequent owner(s) (each and all, an "owner") of an original work of
+authorship and/or a database (each, a "Work").
 
-THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES
-WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF
-MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR
-ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES
-WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN
-ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF
-OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.
+Certain owners wish to permanently relinquish those rights to a Work for the
+purpose of contributing to a commons of creative, cultural and scientific
+works ("Commons") that the public can reliably and without fear of later
+claims of infringement build upon, modify, incorporate in other works, reuse
+and redistribute as freely as possible in any form whatsoever and for any
+purposes, including without limitation commercial purposes. These owners may
+contribute to the Commons to promote the ideal of a free culture and the
+further production of creative, cultural and scientific works, or to gain
+reputation or greater distribution for their Work in part through the use and
+efforts of others.
+
+For these and/or other purposes and motivations, and without any expectation
+of additional consideration or compensation, the person associating CC0 with a
+Work (the "Affirmer"), to the extent that he or she is an owner of Copyright
+and Related Rights in the Work, voluntarily elects to apply CC0 to the Work
+and publicly distribute the Work under its terms, with knowledge of his or her
+Copyright and Related Rights in the Work and the meaning and intended legal
+effect of CC0 on those rights.
+
+1. Copyright and Related Rights. A Work made available under CC0 may be
+protected by copyright and related or neighboring rights ("Copyright and
+Related Rights"). Copyright and Related Rights include, but are not limited
+to, the following:
+
+  i. the right to reproduce, adapt, distribute, perform, display, communicate,
+  and translate a Work;
+
+  ii. moral rights retained by the original author(s) and/or performer(s);
+
+  iii. publicity and privacy rights pertaining to a person's image or likeness
+  depicted in a Work;
+
+  iv. rights protecting against unfair competition in regards to a Work,
+  subject to the limitations in paragraph 4(a), below;
+
+  v. rights protecting the extraction, dissemination, use and reuse of data in
+  a Work;
+
+  vi. database rights (such as those arising under Directive 96/9/EC of the
+  European Parliament and of the Council of 11 March 1996 on the legal
+  protection of databases, and under any national implementation thereof,
+  including any amended or successor version of such directive); and
+
+  vii. other similar, equivalent or corresponding rights throughout the world
+  based on applicable law or treaty, and any national implementations thereof.
+
+2. Waiver. To the greatest extent permitted by, but not in contravention of,
+applicable law, Affirmer hereby overtly, fully, permanently, irrevocably and
+unconditionally waives, abandons, and surrenders all of Affirmer's Copyright
+and Related Rights and associated claims and causes of action, whether now
+known or unknown (including existing as well as future claims and causes of
+action), in the Work (i) in all territories worldwide, (ii) for the maximum
+duration provided by applicable law or treaty (including future time
+extensions), (iii) in any current or future medium and for any number of
+copies, and (iv) for any purpose whatsoever, including without limitation
+commercial, advertising or promotional purposes (the "Waiver"). Affirmer makes
+the Waiver for the benefit of each member of the public at large and to the
+detriment of Affirmer's heirs and successors, fully intending that such Waiver
+shall not be subject to revocation, rescission, cancellation, termination, or
+any other legal or equitable action to disrupt the quiet enjoyment of the Work
+by the public as contemplated by Affirmer's express Statement of Purpose.
+
+3. Public License Fallback. Should any part of the Waiver for any reason be
+judged legally invalid or ineffective under applicable law, then the Waiver
+shall be preserved to the maximum extent permitted taking into account
+Affirmer's express Statement of Purpose. In addition, to the extent the Waiver
+is so judged Affirmer hereby grants to each affected person a royalty-free,
+non transferable, non sublicensable, non exclusive, irrevocable and
+unconditional license to exercise Affirmer's Copyright and Related Rights in
+the Work (i) in all territories worldwide, (ii) for the maximum duration
+provided by applicable law or treaty (including future time extensions), (iii)
+in any current or future medium and for any number of copies, and (iv) for any
+purpose whatsoever, including without limitation commercial, advertising or
+promotional purposes (the "License"). The License shall be deemed effective as
+of the date CC0 was applied by Affirmer to the Work. Should any part of the
+License for any reason be judged legally invalid or ineffective under
+applicable law, such partial invalidity or ineffectiveness shall not
+invalidate the remainder of the License, and in such case Affirmer hereby
+affirms that he or she will not (i) exercise any of his or her remaining
+Copyright and Related Rights in the Work or (ii) assert any associated claims
+and causes of action with respect to the Work, in either case contrary to
+Affirmer's express Statement of Purpose.
+
+4. Limitations and Disclaimers.
+
+  a. No trademark or patent rights held by Affirmer are waived, abandoned,
+  surrendered, licensed or otherwise affected by this document.
+
+  b. Affirmer offers the Work as-is and makes no representations or warranties
+  of any kind concerning the Work, express, implied, statutory or otherwise,
+  including without limitation warranties of title, merchantability, fitness
+  for a particular purpose, non infringement, or the absence of latent or
+  other defects, accuracy, or the present or absence of errors, whether or not
+  discoverable, all to the greatest extent permissible under applicable law.
+
+  c. Affirmer disclaims responsibility for clearing rights of other persons
+  that may apply to the Work or any use thereof, including without limitation
+  any person's Copyright and Related Rights in the Work. Further, Affirmer
+  disclaims responsibility for obtaining any necessary consents, permissions
+  or other rights required for any use of the Work.
+
+  d. Affirmer understands and acknowledges that Creative Commons is not a
+  party to this document and has no duty or obligation with respect to this
+  CC0 or use of the Work.
+
+For more information, please see
+<http://creativecommons.org/publicdomain/zero/1.0/>
diff --git a/package.json b/package.json
index 006dca5..2c2a535 100644
--- a/package.json
+++ b/package.json
@@ -1,16 +1,25 @@
 {
-  "name": "tmw-api",
-  "version": "0.1.0",
-  "description": "TMW RESTful API",
-  "main": "server.js",
-  "scripts": {
-    "test": "echo \"Error: no test specified\" && exit 1"
-  },
-  "author": "The Mana World",
-  "license": "ISC",
-  "dependencies": {
-    "body-parser": "^1.18.2",
-    "express": "^4.16.3",
-    "mysql": "^2.15.0"
-  }
+    "name": "tmw-api",
+    "version": "0.1.0",
+    "description": "TMW RESTful API",
+    "author": "The Mana World",
+    "license": "CC0-1.0",
+    "repository": {
+        "type": "git",
+        "url": "https://github.com/themanaworld/api.git"
+    },
+    "bugs": {
+        "url": "https://github.com/themanaworld/api/issues"
+    },
+    "main": "server.js",
+    "private": true,
+    "scripts": {
+        "test": "echo \"Error: no test specified\" && exit 1",
+        "start": "node server.js"
+    },
+    "dependencies": {
+        "body-parser": "^1.18.2",
+        "express": "^4.16.3",
+        "mysql": "^2.15.0"
+    }
 }
diff --git a/server.js b/server.js
index ac45cc5..3585be1 100644
--- a/server.js
+++ b/server.js
@@ -5,13 +5,6 @@ const https = require("https");
 const config = require("./config.json");
 const api = express();
 
-const db = mysql.createConnection({
-    host     : config.sql.host,
-    user     : config.sql.user,
-    password : config.sql.password,
-    database : config.sql.database
-});
-
 const checkCaptcha = (req, res, next) => {
     const token = String(req.get("X-CAPTCHA-TOKEN"));
 
@@ -20,6 +13,7 @@ const checkCaptcha = (req, res, next) => {
             status: "error",
             error: "no token sent"
         });
+        console.info("a request with an empty token was received");
         return;
     }
 
@@ -33,6 +27,7 @@ const checkCaptcha = (req, res, next) => {
                     status: "error",
                     error: "captcha validation failed"
                 });
+                console.info("a request failed to validate");
                 return;
             }
 
@@ -44,6 +39,7 @@ const checkCaptcha = (req, res, next) => {
             status: "error",
             error: "recaptcha couldn't be reached"
         });
+        console.warn("reCaptcha couldn't be reached");
         return;
     })
 };
@@ -64,44 +60,58 @@ api.post("/api/account", (req, res) => {
             status: "error",
             error: "malformed request"
         });
+        console.info("a malformed request was received");
         return;
     }
 
-    let account = {
+    const account = {
         username: req.body.username,
         password: req.body.password,
         email: req.body.email || "a@a.com"
     };
 
-    db.connect();
-    db.query(`SELECT COUNT(*) FROM ${config.sql.table} WHERE USERNAME="${account.username}"`, (err, rows, fields) => {
+    const db = mysql.createConnection({
+        host     : config.sql.host,
+        user     : config.sql.user,
+        password : config.sql.password,
+        database : config.sql.database
+    });
+
+    db.connect(err => {
         if (err) {
             res.status(500).json({
                 status: "error",
                 error: "couldn't reach the database"
             });
-        } else if (rows[0].count > 0) {
-            res.status(409).json({
-                status: "error",
-                error: "already exists"
-            });
-        } else {
-            db.query(`INSERT INTO ${config.sql.table} (USERNAME, PASSWORD, EMAIL, GENDER) VALUES ("${account.username}", "${account.password}", "${account.email}", "N")`, (err, rows, fields) => {
-                if (err) {
-                    res.status(500).json({
+            console.warn("a connection with the database couldn't be established");
+            return;
+        }
+
+        db.query({sql: `INSERT INTO ${config.sql.table} (USERNAME, PASSWORD, EMAIL, GENDER) VALUES ("${account.username}", "${account.password}", "${account.email}", "N")`}, (err, rows, fields) => {
+            if (err) {
+                if (err.code == "ER_DUP_ENTRY") {
+                    res.status(409).json({
                         status: "error",
-                        error: "couldn't add the user"
+                        error: "already exists"
                     });
+                    console.info("a request to create an already-existent account was received");
                 } else {
-                    res.status(201).json({
-                        status: "success"
+                    res.status(500).json({
+                        status: "error",
+                        error: "couldn't add the user"
                     });
+                    console.error("an unexpected sql error occured", err);
                 }
-            });
-        }
-    });
+            } else {
+                res.status(201).json({
+                    status: "success"
+                });
+                console.info(`an account was created: ${account.username}`);
+            }
 
-    db.close();
+            db.end();
+        });
+    });
 });
 
 
@@ -111,6 +121,7 @@ api.use((req, res, next) => {
         status: "error",
         error: "unknown endpoint"
     });
+    console.info("a request for an unknown endpoint was received");
 });
 
 api.set("trust proxy", "loopback"); // only allow localhost to communicate with the API