[Nodejs]생활코딩 Nodejs-MySQL 2

Nodejs-Mysql 연결

db.query(`SELECT * FROM topic WHERE id=${queryData.id}`,function(error2,topic){

는 사용자의 입력값을 그대로 받아 들이는 것인데,

db.query(`SELECT * FROM topic WHERE id=?`,[queryData.id],function(error2,topic){

입력값을 받을 자리를 ? 로 하고 두번째 인자에 배열로 입려값을 담아 주면,
?자리에 값을 치환해 주면서 공격의 가능성이 있는 코드를 세탁 해준다.(어느정도로 해주는지는 모르겠음…)

Mysql에서 마지막 삽입한 id값을 얻는 방법

(보충공부 필요함)

마지막으로 삽입한 id값을 변수에 저장해 사용하는 방법.

	INSERT INTO table1 (title,userid) VALUES ('test', 1); 
	SET @last_id_in_table1 = LAST_INSERT_ID();
	INSERT INTO table2 (parentid,otherid,userid) VALUES (@last_id_in_table1, 4, 1); 

테이블의 id중 최대값을 select 해와서 사용 하는 방법

	INSERT INTO table1 (title,userid) VALUES ('test', 1); 
	INSERT INTO table2 (parentid,otherid,userid) VALUES (LAST_INSERT_ID(), 4, 1); 
	SELECT MAX(id) FROM table1;

nodejs의 경우 callback 함수에 반환되는 insertedId로 얻을 수 있다.

   db.query(
           `INSERT INTO topic (title,description,created,author_id) 
                  VALUES(?,?,NOW(),?)`,
                  [post.title,post.description,1],
                  function(error,result){
                      if(error){
                        throw error;
                      }
                      response.writeHead(302,{Location: `/?id=${result.insertId}`});
                       response.end();
                  }
                )

변경된 main.js

var http = require('http');
var fs = require('fs');
var url = require('url');
var qs = require('querystring');
var template = require('./lib/template.js');
var path = require('path');
var sanitizeHtml = require('sanitize-html');
var mysql = require('mysql');
var db = mysql.createConnection({
  host : 'localhost',
  user : 'root',
  password : '*********',
  database : 'opentutorials'
});
db.connect();

var app = http.createServer(function(request,response){
    var _url = request.url;
    var queryData = url.parse(_url, true).query;
    var pathname = url.parse(_url, true).pathname;
    if(pathname === '/'){
      if(queryData.id === undefined){
        db.query(`SELECT * FROM topic`,function(error,topics){
          var title = 'Welcome';
          var description = 'Hello, Node.js';
          var list = template.list(topics);
          var html = template.HTML(title, list,
                `<h2>${title}</h2>${description}`,
                `<a href="/create">create</a>`
              );
          response.writeHead(200);
          response.end(html);
        });
      } else {
        // fs.readdir('./data', function(error, filelist){
        //   var filteredId = path.parse(queryData.id).base;
        //   fs.readFile(`data/${filteredId}`, 'utf8', function(err, description){
        //     var title = queryData.id;
        //     var sanitizedTitle = sanitizeHtml(title);
        //     var sanitizedDescription = sanitizeHtml(description, {
        //       allowedTags:['h1']
        //     });
        //     var list = template.list(filelist);
        //     var html = template.HTML(sanitizedTitle, list,
        //       `<h2>${sanitizedTitle}</h2>${sanitizedDescription}`,
        //       ` <a href="/create">create</a>
        //         <a href="/update?id=${sanitizedTitle}">update</a>
        //         <form action="delete_process" method="post">
        //           <input type="hidden" name="id" value="${sanitizedTitle}">
        //           <input type="submit" value="delete">
        //         </form>`
        //     );
        //     response.writeHead(200);
        //     response.end(html);
        //   });
        // });
        db.query(`SELECT * FROM topic`,function(error,topics){
          if(error){
            throw error;
          }
          db.query(`SELECT * FROM topic WHERE id=${queryData.id}`,function(error2,topic){
            if(error2){
              throw error2;
            }
            var title = topic[0].title;
            var description = topic[0].description;
            var list = template.list(topics);
            var html = template.HTML(title, list,
                  `<h2>${title}</h2>${description}`,
                  ` <a href="/create?id=${queryData.id}">create</a>
                    <a href="/update?id=${queryData.id}">update</a>
                    <form action="delete_process" method="post">
                      <input type="hidden" name="id" value="${queryData.id}">
                     <input type="submit" value="delete">
                    </form>`
                );
            response.writeHead(200);
            response.end(html);
          })
          
        });

      }
    } else if(pathname === '/create'){
      db.query(`SELECT * FROM topic`,function(error,topics){
        var title = 'Create';
        var list = template.list(topics);
        var html = template.HTML(title, list,
              `<form action="/create_process" method="post">
                <p><input type="text" name="title" placeholder="title"></p>
                <p>
                  <textarea name="description" placeholder="description"></textarea>
                </p>
                <p>
                  <input type="submit">
                </p>
              </form>`
            ,`<a href="/create">create</a>`);
        response.writeHead(200);
        response.end(html);
      });
    } else if(pathname === '/create_process'){
      var body = '';
      request.on('data', function(data){
          body = body + data;
      });
      request.on('end', function(){
          var post = qs.parse(body);
         db.query(
           `INSERT INTO topic (title,description,created,author_id) 
                  VALUES(?,?,NOW(),?)`,
                  [post.title,post.description,1],
                  function(error,result){
                    console.log('res: ',result);
                      if(error){
                        throw error;
                      }
                      response.writeHead(302,{Location: `/?id=${result.insertId}`});
                       response.end();
                  }
                )
      });
    } else if(pathname === '/update'){
      fs.readdir('./data', function(error, filelist){
        var filteredId = path.parse(queryData.id).base;
        fs.readFile(`data/${filteredId}`, 'utf8', function(err, description){
          var title = queryData.id;
          var list = template.list(filelist);
          var html = template.HTML(title, list,
            `
            <form action="/update_process" method="post">
              <input type="hidden" name="id" value="${title}">
              <p><input type="text" name="title" placeholder="title" value="${title}"></p>
              <p>
                <textarea name="description" placeholder="description">${description}</textarea>
              </p>
              <p>
                <input type="submit">
              </p>
            </form>
            `,
            `<a href="/create">create</a> <a href="/update?id=${title}">update</a>`
          );
          response.writeHead(200);
          response.end(html);
        });
      });
    } else if(pathname === '/update_process'){
      var body = '';
      request.on('data', function(data){
          body = body + data;
      });
      request.on('end', function(){
          var post = qs.parse(body);
          var id = post.id;
          var title = post.title;
          var description = post.description;
          fs.rename(`data/${id}`, `data/${title}`, function(error){
            fs.writeFile(`data/${title}`, description, 'utf8', function(err){
              response.writeHead(302, {Location: `/?id=${title}`});
              response.end();
            })
          });
      });
    } else if(pathname === '/delete_process'){
      var body = '';
      request.on('data', function(data){
          body = body + data;
      });
      request.on('end', function(){
          var post = qs.parse(body);
          var id = post.id;
          var filteredId = path.parse(id).base;
          fs.unlink(`data/${filteredId}`, function(error){
            response.writeHead(302, {Location: `/`});
            response.end();
          })
      });
    } else {
      response.writeHead(404);
      response.end('Not found');
    }
});
app.listen(3000);