Accessing Amazon RDS from javascript

Question

I'm interning for a company and have been given an assignment. I'm to write a Javascript script (it's for internal use only; security is not a concern) that accesses an RDS database on an AWS instance, grabs a list of email addresses, and uses the server's smtp to send emails to the whole list. The problem is that I know nothing about AWS and RDS. Here are the things I was provided:

--Server address, port, and credentials file of smtp server

--Address of AWS DB, and its username, password, database name, and table

--The company's server url, security key, and I was also given SSH and SCP commands.

Where do I start learning how to do this? I feel like it's within my grasp, but I just don't know the overall process of what I need to do to get this information. I've never used SQL or RDS before. Any direction whatsoever would be appreciated!

I really doubt that you can do this in pure Javascript, unless you can use Node.js, or Ajax to a server side script that executes the commands. Even if it is just for internal use, there would still be massive security problems because all the commands and credentials would easily be accessible. — SteveEdson, Nov 03 '13 at 23:06
That's what I was afraid of... Oh well. I have the option of doing it in PHP, which I'll have to teach myself first, but that won't be a problem--just more time consuming. — Taylor Hill, Nov 03 '13 at 23:14
If you know javascript, you should be able to do this using node.js. — datasage, Nov 03 '13 at 23:15

score 0 · Answer 1 · edited May 23 '17 at 11:48

Try to break the problem down into chunks:

Write your Database Query as SQL

If the RDS database instance in question is publicly available, try to connect to it with your preferred (GUI?) SQL client, and inspect its tables. Figure out how to write the SQL query you need in order to grab the e-mail addresses you'll need in the next step, e.g. select email_address from users where user_type='Internal'. The w3schools SQL Introduction is a good place to start.

If the RDS database instance isn't publicly available (i.e. if you can only connect to the database from within AWS - from the server on which you plan to run your code), then figure out how to log in to that server using SSH or RDP, and then use a database client on your AWS server to write and test your SQL query.

Check first that you're not logging into a production machine that you could break by mistake.

Use a Server-Side program to run your SQL query

You must then connect to the RDS database instance using some code that runs server-side. If you're using a server-side JavaScript environment such as NodeJS, this is fine, but whilst it's potentially feasible to use JavaScript directly from a browser to connect to a database, it's not good practice, even for your assignment, so make sure you're using some server-side code.

Find out what your company's usual platform is (Java? Python? PHP? NodeJS?) and go with that. Search the internet for example code - you'll easily find some, because querying a database is an extremely common programming task. Since you've already got your SQL query, you'll be able to write some simple code that connects to the database and grabs your desired email addresses. For now, just have it print them out to a log file or to the console.

Send a test e-mail

Separately, in your server-side program, figure out how to connect to your company's SMTP server and send an e-mail to yourself, as a start. Again, search the internet for examples in your chosen programming language.

Once you've done that, define two or three e-mail addresses in an array or list, and figure out how to send the same message to these users simultaneously.

Connect your Database Query Code and SMTP Code

You're able to query the database, and you're able to send an e-mail to multiple users. Now add to your code to join those two functions together. Don't spam everyone in your firm and get yourself fired!

You mentioned JavaScript

Make sure you are clear about the requirements of your assignment. Should the program be started from a browser? Does a user need to click a button on a webpage saying 'Send Email'? If so, figure out how to submit a request to your server to have the server-side code execute. Search the internet for examples of POSTing HTML forms from JavaScript.

Good luck!