1937
No
인프런 : https://www.inflearn.com/course/web2-node-js-mysql#
생활 코딩 : https://opentutorials.org/module/3560/21175
유튜브 목록 : https://www.youtube.com/playlist?list=PLuHgQVnccGMAicFFRh8vFFFtLLlNojWUh
소스 : https://github.com/web-n/node.js-mysql
https://github.com/web-n/node.js-mysql/releases/tag/1
https://github.com/braverokmc79/Nodejs-MySQL
저자 관련 기능 구현
20. 저자 관리 기능의 구현
21. 저자 목록 보기 기능 구현
소스코드
main.js
} else if (pathname === '/author') {
author.home(request, response);
}
lib/author.js
var template = require('./template.js');
var db = require("./db");
exports.home = function (request, response) {
db.query("SELECT * FROM topic", function (error, topics) {
db.query("SELECT * FROM author", function (error, authors) {
var title = 'Author';
var list = template.list(topics);
var html = template.HTML(title, list,
`
${template.authorTable(authors)}
<style>
table{border-collapse:collapse;}
td{border:1px solid black;}
</style>
`,
`<a href="/create">create</a>`
);
response.writeHead(200);
response.end(html);
});
});
}
template.js
authorTable(authors) {
var tag = authors.map(author => (
`<tr>
<td>${author.name}</td>
<td>${author.profile}</td>
<td>update</td>
<td>delete</td>
</tr>`
)).join("");
return `<table> ${tag} </table>`;
}
22. 저자 생성 기능 구현
소스코드
main.js
} else if (pathname === '/author') {
author.home(request, response);
} else if (pathname === '/author/create_process') {
author.create_process(request, response);
}
/lib/author.js
exports.create_process = function (request, response) {
var body = '';
request.on('data', function (data) {
body = body + data;
});
request.on('end', function () {
var post = qs.parse(body);
db.query(`INSERT INTO author(name, profile) VALUES(?, ?)`,
[post.name, post.profile], function (error, result) {
if (error) throw error;
response.writeHead(302, { Location: `/? id = ${result.insertId} ` });
response.end();
});
});
}
23. 저자 수정 기능 구현 -1
소스코드
main.js
} else if (pathname === '/author/update') {
author.update(request, response);
} else {
author
exports.update = function (request, response) {
var _url = request.url;
var queryData = url.parse(_url, true).query;
db.query("SELECT * FROM topic", function (error, topics) {
db.query("SELECT * FROM author", function (error, authors) {
db.query("SELECT * FROM author WHERE id=?", [queryData.id], function (error2, author) {
var title = 'Author';
var list = template.list(topics);
var html = template.HTML(title, list,
`
${template.authorTable(authors)}
<style>
table{border-collapse:collapse;}
td{border:1px solid black;}
</style>
<h3>업데이트</h3>
<form action="/author/create_process" method="post">
<p>
<input type="hidden" name="id" value="${queryData.id}" />
</p>
<p>
<input type="text" name="name" value="${author[0].name}" placeholder="name">
</p>
<p>
<textarea name="profile" placeholder="description" >${author[0].profile}</textarea>
</p>
<p>
<input type="submit">
</p>
</form>
`,
''
);
response.writeHead(200);
response.end(html);
});
});
});
}
24. 저자 수정 기능 구현 -2
소스코드
lib/author.js
exports.update_process = function (request, response) {
var body = '';
request.on('data', function (data) {
body = body + data;
});
request.on('end', function () {
var post = qs.parse(body);
db.query(`UPDATE author SET name =?, profile =? WHERE id=?`,
[post.name, post.profile, post.id], function (error, result) {
if (error) throw error;
response.writeHead(302, { Location: `/author` });
response.end();
});
});
}
25. 저자 삭제 기능 구현
lib/template.js
~
authorTable(authors) {
var tag = authors.map(author => (
`<tr>
<td>${author.name}</td>
<td>${author.profile}</td>
<td><a href="/author/update?id=${author.id}">update</a></td>
<td>
<form action="/author/delete_process" method="post">
<input type="hidden" name="id" value="${author.id}" >
<input type="submit" value="delete" >
</form>
</td>
</tr>`
)).join("");
return `<table> ${tag} </table>`;
}
~
main.js
} else if (pathname === '/author/delete_process') {
author.delete_process(request, response);
}
lib/author.js
exports.delete_process = function (request, response) {
var body = '';
request.on('data', function (data) {
body = body + data;
});
request.on('end', function () {
var post = qs.parse(body);
db.query(`DELETE FROM topic WHERE author_id=?`, [post.id], function (error, result) {
if (error) throw error;
db.query(`DELETE FROM author WHERE id=?`, [post.id], function (error2, result) {
if (error2) throw error;
response.writeHead(302, { Location: `/author` });
response.end();
});
});
});
}
보안
26. SQL Injection 1
27. SQL Injection 2
28. Escaping
소스코드
lib/topic.js
var url = require('url');
var qs = require('querystring');
var template = require('./template.js');
var db = require("./db");
var sanitizeHTML = require("sanitize-html");
exports.home = function (request, response) {
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);
});
}
exports.page = function (request, response) {
var _url = request.url;
var queryData = url.parse(_url, true).query;
db.query("SELECT * FROM topic", function (error, topics) {
if (error) throw error;
db.query(`SELECT * FROM topic LEFT JOIN author ON topic.author_id=author.id WHERE topic.id=?`, [queryData.id], function (error2, topic) {
if (error2) throw error2;
console.log("topic : ", topic);
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}
<p>by ${topic[0].name}</p>
`,
` <a href="/create">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);
});
});
}
exports.create = function (request, response) {
db.query("SELECT * FROM topic ", function (error, topics) {
db.query("SELECT * FROM author ", function (error2, authors) {
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>
${template.authorSelect(authors)}
</p>
<p>
<input type="submit">
</p>
</form >
`, '');
response.writeHead(200);
response.end(html);
});
});
}
exports.create_process = function (request, response) {
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(), ?)`,
[sanitizeHTML(post.title), sanitizeHTML(post.description), sanitizeHTML(post.author)], function (error, result) {
if (error) throw error; //등록 처리된 아이디 값 가져오기 insertId
response.writeHead(302, { Location: `/? id = ${result.insertId} ` });
response.end();
});
});
}
exports.update = function (request, response) {
var _url = request.url;
var queryData = url.parse(_url, true).query;
db.query("select * from topic", function (erro, topics) {
if (erro) throw erro;
db.query(`SELECT * FROM topic WHERE id =? `, [queryData.id], function (error2, topic) {
db.query("SELECT * FROM author ", function (error2, authors) {
if (error2) throw error2;
var list = template.list(topics);
var html = template.HTML(topic[0].title, list,
`
<form action = "/update_process" method = "post" >
<input type="hidden" name="id" value="${topic[0].id}">
<p><input type="text" name="title" placeholder="title" value="${topic[0].title}"></p>
<p>
<textarea name="description" placeholder="description">${topic[0].description}</textarea>
</p>
<p>
${template.authorSelect(authors, topic[0].author_id)}
</p>
<p>
<input type="submit">
</p>
</form>
`,
`<a href = "/create" > create</a > <a href="/update?id=${topic[0].id}">update</a>`
);
response.writeHead(200);
response.end(html);
});
});
});
}
exports.update_process = function (request, response) {
var body = '';
request.on('data', function (data) {
body = body + data;
});
request.on('end', function () {
var post = qs.parse(body);
db.query(`UPDATE topic SET title =?, description =?, author_id =? WHERE id =? `,
[sanitizeHTML(post.title), sanitizeHTML(post.description), sanitizeHTML(post.author), post.id], function (error, result) {
if (error) throw error;
response.writeHead(302, { Location: `/? id = ${post.id} ` });
response.end();
});
});
}
exports.delete_process = function (request, response) {
var body = '';
request.on('data', function (data) {
body = body + data;
});
request.on('end', function () {
var post = qs.parse(body);
db.query("delete from topic where id=?", [post.id], function (error, result) {
if (error) throw error;
response.writeHead(302, { Location: `/ ` });
response.end();
});
});
}
lib/author.js
var url = require('url');
var qs = require('querystring');
var template = require('./template.js');
var db = require("./db");
var sanitizeHTML = require("sanitize-html");
exports.home = function (request, response) {
db.query("SELECT * FROM topic", function (error, topics) {
db.query("SELECT * FROM author", function (error, authors) {
var title = 'Author';
var list = template.list(topics);
var html = template.HTML(title, list,
`
${template.authorTable(authors)}
<style>
table{border-collapse:collapse;}
td{border:1px solid black;}
</style>
<form action="/author/create_process" method="post">
<p>
<input type="text" name="name" placeholder="name">
</p>
<p>
<textarea name="profile" placeholder="description" ></textarea>
</p>
<p>
<input type="submit">
</p>
</form>
`,
''
);
response.writeHead(200);
response.end(html);
});
});
}
exports.create_process = function (request, response) {
var body = '';
request.on('data', function (data) {
body = body + data;
});
request.on('end', function () {
var post = qs.parse(body);
db.query(`INSERT INTO author(name, profile) VALUES(?, ?)`,
[sanitizeHTML(post.name), sanitizeHTML(post.profile)], function (error, result) {
if (error) throw error;
response.writeHead(302, { Location: `/author` });
response.end();
});
});
}
exports.update = function (request, response) {
var _url = request.url;
var queryData = url.parse(_url, true).query;
db.query("SELECT * FROM topic", function (error, topics) {
db.query("SELECT * FROM author", function (error, authors) {
db.query("SELECT * FROM author WHERE id=?", [queryData.id], function (error2, author) {
var title = 'Author';
var list = template.list(topics);
var html = template.HTML(title, list,
`
${template.authorTable(authors)}
<style>
table{border-collapse:collapse;}
td{border:1px solid black;}
</style>
<h3>업데이트</h3>
<form action="/author/update_process" method="post">
<p>
<input type="hidden" name="id" value="${queryData.id}" />
</p>
<p>
<input type="text" name="name" value="${author[0].name}" placeholder="name">
</p>
<p>
<textarea name="profile" placeholder="description" >${author[0].profile}</textarea>
</p>
<p>
<input type="submit">
</p>
</form>
`,
''
);
response.writeHead(200);
response.end(html);
});
});
});
}
exports.update_process = function (request, response) {
var body = '';
request.on('data', function (data) {
body = body + data;
});
request.on('end', function () {
var post = qs.parse(body);
db.query(`UPDATE author SET name =?, profile =? WHERE id=?`,
[sanitizeHTML(post.name), sanitizeHTML(post.profile), post.id], function (error, result) {
if (error) throw error;
response.writeHead(302, { Location: `/author` });
response.end();
});
});
}
exports.delete_process = function (request, response) {
var body = '';
request.on('data', function (data) {
body = body + data;
});
request.on('end', function () {
var post = qs.parse(body);
db.query(`DELETE FROM topic WHERE author_id=?`, [post.id], function (error, result) {
if (error) throw error;
db.query(`DELETE FROM author WHERE id=?`, [post.id], function (error2, result) {
if (error2) throw error;
response.writeHead(302, { Location: `/author` });
response.end();
});
});
});
}
29. 수업을 마치며
댓글 ( 4)
댓글 남기기