Making sql insert statement safe

Question

I'm a newbie in the realm of SQL still, so I have a question about whether I'm using a safe practice.
I have a user table with a column for extra notes, which should be able to contain pretty much anything a person can type in. I'm taking care of (or I think I am) the single quotes in the notes field by doing

notes = notes.replace("'", "''");

Then, the query I will execute is put together like this:

String query = "INSERT into users (username, password, notes) VALUES('" + username + "' , '" + password + "' , '" + notes + "'";

Aside from the password not being encrypted, what else might I be missing here? I'm not expecting that many hackers will even know of the existence of this software, but then again, nobody expects their code to be hacked.

If you state your platform people can link to the corresponding methods used to generate parameterized queries which take care of escaping values for you as well as generally being more flexible/readable. — Alex K.
– Alex K., Commented Nov 14, 2011 at 15:50
Sorry, platform is Java, though I just needed to be introduced to the topic of parameterized queries. — Indigenuity
– Indigenuity, Commented Nov 14, 2011 at 16:25

Curtis · Accepted Answer · 2011-11-14 15:49:41Z

3

Always use parameterised queries.

Using ASP.NET C#:

SqlCommand cmd = new SqlCommand("INSERT INTO TableA (Username, Password, Notes) VALUES (@Username , @Password, @Notes)", Conn);
cmd.parameters.add("@Username",sqldbtype.nvarchar).value = username;
cmd.parameters.add("@Password",sqldbtype.nvarchar).value = password;
cmd.parameters.add("@Notes",sqldbtype.nvarchar).value = notes;
cmd.executenonquery();

Then you don't need to worry about single quotes, or SQL Injection.

answered Nov 14, 2011 at 15:49

Curtis

104k69 gold badges280 silver badges359 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hugh Jones Over a year ago

plus - many servers will be able to cache a plan for a parameterized query and hence will perform quicker.

Indigenuity Over a year ago

Thanks! I didn't mention my platform, sorry, but you gave me the answer I needed, even if it is a different platform.

Eugene · Accepted Answer · 2011-11-14 15:51:28Z

2

Google for "prepared statements" - a secure way to execute queries with users input.

answered Nov 14, 2011 at 15:51

Eugene

3,37523 silver badges17 bronze badges

Comments

Nonym · Accepted Answer · 2011-11-14 15:52:55Z

2

Sorry, I have no privilege to comment just yet, but what I can say will help is to utilize stored procedures, and to use object-oriented style of coding to use parameters.

You can also check this site out as it provides a lot of info on improving your security (which includes the fight against SQL injection): https://www.owasp.org/index.php/Main_Page

answered Nov 14, 2011 at 15:52

Nonym

6,2971 gold badge27 silver badges21 bronze badges

1 Comment

kol Over a year ago

+1 for mentioning stored procedures. Concatenating SQL commands is very dangerous.

Collectives™ on Stack Overflow

Making sql insert statement safe

3 Answers 3

2 Comments

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Related