0

I am using this statement below as a database query to MySQL. The Query works fine as far as retrieving the data from both the posts and users table, however when I log the query it also logs the password column from my users table. I would like to select specific columns from both the posts and users table that are produced from the specific query. I am not sure how the INNER JOINING of two tables is treated in MySQL

I was wondering if someone can show me how to properly format this so that the password column is excluded from my results.

'SELECT * FROM posts INNER JOIN users ON posts.author = users.id WHERE posts.id = ?'

I am using NodeJS and MySQL so when I console.log I get this result

[ RowDataPacket {
    id: 62,
    title: 'This is the title',
    body: 'This is the body ',
    author: '62',
    createdOn: 2018-02-23T02:20:24.000Z,
    name: 'Andrew',
    email: 'andrew.lee@gmail.com',
    username: 'thexp',
    password: '$2a$10$lfUj.JkuotihrXeYRVPoTuWWdWfGHnGdTSEzv8fhaSrPa3m98Vg.G'  <---- I would like this to not show } ]

Any help is appreciated. Thanks!

Andrew Lee
  • 304
  • 5
  • 14
  • List all columns that you want to show – NiVeR Feb 27 '18 at 20:45
  • 3
    Don't `SELECT *` from posts, just select the columns you need. – C. Helling Feb 27 '18 at 20:45
  • Possible duplicate of [Select all columns except one in MySQL?](https://stackoverflow.com/questions/9122/select-all-columns-except-one-in-mysql) – But those new buttons though.. Feb 27 '18 at 20:47
  • Thanks, I got it working with `SELECT title, body, author, createdOn, name, email, username FROM posts INNER JOIN users ON posts.author = users.id WHERE posts.id = ?` – Andrew Lee Feb 27 '18 at 20:48
  • Also I don't know how you're calling it, but the `WHERE posts.id = ?` part looks like it might be vulnerable to SQL injection. – C. Helling Feb 27 '18 at 20:49
  • I am calling it like so 'db.query(SELECT title, body, author, createdOn, name, email, username FROM posts INNER JOIN users ON posts.author = users.id WHERE posts.id = ?, [id])'. Is there a better way of doing this? @C.Helling – Andrew Lee Feb 27 '18 at 20:50
  • @Publifiedlabs I meant it depends on how you're getting that `[id]`. If it is coming directly from user input, for example, it's definitely vulnerable. If there's any way for a user to modify that value that gets passed to the query, then it could be vulnerable. – C. Helling Feb 27 '18 at 20:54
  • ahh, apologies @C.Helling. I am getting from req.params.id. So I don't think it's from a users input nor can the user modify it. – Andrew Lee Feb 27 '18 at 20:57
  • @billynoah, apologies if this may have been a duplicate. I tried reading that post but I didn't understand that by selecting the column from SELECT that it is also applied to the INNER JOIN table as well. I am a noob. – Andrew Lee Feb 27 '18 at 21:02
  • 1
    @Publifiedlabs Forgive me since it's been a few years since I worked with Node.js. If you use POSTMAN, can you modify the `id` in the request URL to something else entirely and get it passed to your query? From what I read (https://stackoverflow.com/questions/15778572/preventing-sql-injection-in-node-js), the `node-mysql` lib already escapes queries, but I did notice e.g. `mysql` formatting being used: https://github.com/mysqljs/mysql#preparing-queries – C. Helling Feb 27 '18 at 21:12
  • @C.Helling, thanks for all the help! I think I have it working now. When I pass a GET request in POSTMAN with an id that does not exist, It does not return any data from the database. – Andrew Lee Feb 28 '18 at 18:18

1 Answers1

2

Thanks to the help of other users I have learned that by INNER JOINING a table MySQL will treat it as if it was one table.

So simply I just had to specify all columns that I want included in both tables in the beginning of the SELECT query.

SELECT title, body, author, createdOn, name, email, username FROM posts INNER JOIN users ON posts.author = users.id WHERE posts.id = req.params.id
Andrew Lee
  • 304
  • 5
  • 14