-1

Let's say a user goes to the log in page of my website and creates an account. Instead of entering their real name, they decide to write some sql code and try to gain access to my database.

I have prepared statements to prevent any harmful code from altering my database at the signup process, however if the user goes to create a post, comment, or reply, my website will use their info like their username to create/display new data in the website.

My main concern is that someone could store code within my database and access it somewhere else when I'm not expecting it.

I am capable of preparing statements for everything involving my database, but my question is different. I want to know if I should be concerned about usernames writing sql code after they become strings in the prepared statements, is the threat eliminated for good or not?

marked as duplicate by BobDalgleish, Robert Harvey Jul 5 at 17:16

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1

It seems like you've not covered your bases.

For the sake of example, let's say your application has two features: logging in and blogging. You'd expect your setup to be something like:

+-----------+     +------------+
|           |     |            |
|   Login   |     |  Blogging  |
|           |     |            |
+---------+-+     +-+----------+
          |         |
          |         |
          v         v
      +---+---------+---+
      |                 |
      |    Database     |
      |                 |
      +-----------------+

This is dramatically oversimplified of course but sufficient for the current answer.

I have prepared statements to prevent any harmful code from altering my database at the signup process

Your question implies that you've put the safeguard in your login logic. But as you've discovered, that doesn't cover all cases, as the blogging feature is still left unguarded.

I am capable of preparing statements for everything involving my database

It would've been much better to implement this safeguarding logic in the database logic. That way, any part of your application that uses the database will be forced to have its data cleaned up before anything hits the DB.

I want to know if I should be concerned about usernames writing sql code after they become strings in the prepared statements

I'm not quite sure what the proposed concern is. Reading your question at face value, it seems like you misunderstand what SQL injection is, since all SQL queries are strings, which means that you can't protect from SQL injection by using strings.

Secondly, the proposed solution of putting the logic in the database logic prevents this issue in all cases all at once. For example, let's say your user enters this as their username:

Robert'); DROP TABLE Students; --

This is a really well known form of SQL injection.

There are many ways to protect against SQL injection (parametrized queries are the way to go), but let's say that for the sake of example, you decide to write a simple cleanup script that removes all non-alphanumeric characters. This means that the actual username you'll be sending to the database will be:

Robert DROP TABLE Students

Which no longer works as a SQL injection attack because it no longer injects anything. The username will still contain some of the words, but as it's become harmless, it's not a problem for you.

Note: don't just roll your own cleanup script, because you might not know about every possible attack vector. Rely on existing cleanup methods so you know you've got a robust way of defending against attacks. I showed you a basic example to showcase the intent of a sanitization, not because you should totally do it yourself.

Note: Guran is correct that SQL injection is not the only thing you should take into account. Malicious users can inject JS which only activates when loaded into another user's browser. I suspect that most JS attacks will already break when being run through a SQL injection sanitizer (because JS and SQL share many syntax characters), but definitely look into that if JS attacks are also something you wish to protect yourself against.

however if the user goes to create a post, comment, or reply, my website will use their info like their username to create/display new data in the website

Any well built relational database (which SQL is the main use case for) is not going to enter the username a second time when that user saves a blog/comment/reply. All you're going to add is a blog/comment/reply entry with a user ID reference, which points to your existing user which you stored during the account creation process.

It's not impossible to use the username as the FK constraint, but it is somewhat impractical. It's much more commonplace to use the PK of the user table, which is usually an int or guid.
As per this SO answer, using a narrow primary key (like an int) has performance and storage benefits over using a wider (and variable length) value like varchar.

As to the possibility of SQL injection attack coming from the content of the blog/comment/reply itself, refer back to my proposed solution of ensuring you sanitize your database input on the database layer, as this automatically ensures that all entered data will be cleaned, and it doesn't require extra implementation work when you start adding features to your application.

  • Thank you for all of this info. It might take me a while to understand everything you're talking about (I'm still new) but I will definitely use this answer to study as I dive deeper. Thank you :) – Cole Jul 5 at 8:13
2

Treat all user input as suspect. Always.

User input (esp in string format) is always suspect. SQL injection (executed at the time of posting or, as in your question later) is only one way that a malicious user could take advantage of security holes.

Another threat to consider is script injection. (wikipedia)

Using a script injection vulnerability, an attacker could for exemple embed javascript in a forum post (or even user name) that redirects users to a spoofed version of your site.

While it is a good practice to filter input for obvious attempts to inject malicious payload, you must never assume that any data from user input is "safe". If you use it in an SQL statement, parameterize it. If you display it in a browser, htmlencode it. Etc.

  • Good to know. Thank you for your help! – Cole Jul 5 at 8:14
0

Prepared statements are the way to go.


Prepared statements aren't strictly speaking the solution; the solution are bind variables which you have to use together with prepared statements.

When you call the DB API that fills values into the bind variables in a prepared statement, the API never concatenates strings, nor interprets the variable values as parts of the statement, but instead tries to fill them verbatim into the database table.

Having the statement prepared is mostly a performance improvement that you could e.g. also apply to SELECT queries - it's bind variables that provide the security benefit by bypassing all concatenation and parsing.

You should still clean the variable values before passing them, though - in a blog comment, for example, it might be worth stripping all (superfluous) HTML tags. An iframe tag loading the wrong content could hurt your uses, even if your DB stays safe.

Not the answer you're looking for? Browse other questions tagged or ask your own question.