Properly protect edit control from SQL injection and nonsense characters

Question

0.00/5 (No votes)

See more:

INTRODUCTION AND RELEVANT INFORMATION:

I have an edit control in which user should enter a company name. Edit control is locale aware.

PROBLEM:

I wish to properly protect it from SQL injection attacks and from user entering nonsense characters.

MY EFFORTS TO SOLVE THE PROBLEM:

I was thinking of discarding colon, and semicolon in my subclassing procedure in response to WM_CHAR, and to perform the validation again in response to EN_CHANGE. If problem occurs I would inform the user and disable "Save" button.

Since my application is locale aware, I need locale aware functions for testing the input character.

So far I have found IsCharAlpha[^] and IsCharAlphaNumeric[^] which seem locale aware ( my English is not the best so maybe I have misread the MSDN documentation, I apologize for that ).

I have also searched here for similar questions but found none.

QUESTION:

Will discarding semicolon only do the trick, or I need to take some other characters into consideration?

Am I on the "right track" with subclassing + EN_CHANGE or is there a better way to achieve this? If there is can you point me in the right direction?

Thank you.

Best regards.

Posted 19-Mar-14 15:33pm

AlwaysLearningNewStuff

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sergey Alexandrovich Kryukov · Accepted Answer · 2014-03-19T16:46:00

Solution 1

This exploit is well explained here, as well as the major remedy:
http://xkcd.com/327[^],
http://en.wikipedia.org/wiki/SQL_injection[^].

As you can see, effective remedy is the use of parametrized statements and the sanitizing of user input from ';' and other characters cannot be reliable, bothersome and should not be used. However, you can sanitize the input of all other purposes, to ensure entering valid data.

Sorry, my past answers are mostly for .NET and ADO.NET, but you can find some useful ideas:
EROR IN UPATE in com.ExecuteNonQuery();[^],
hi name is not displaying in name?[^].

—SA

Posted 19-Mar-14 16:46pm

Sergey Alexandrovich Kryukov

Comments

AlwaysLearningNewStuff 19-Mar-14 23:33pm

There is no need to apologize, I will have to learn .NET one day, and your guidance can only be beneficial to me!

I forgot to mention that I use ADO Command Object to perform parametrized query.

The way I see it, discarding semicolon should "do the job".

It is late now and I must work on some other parts of my project, so I will give this a look tomorrow or in a day or two ( please understand me, I am working on my own on a my first project ).

Still, knowing the quality of your answers I strongly believe I will accept the answer officially.

Thank you for your never ending support and guidance.

P.S. The first link points to a comic book! :)

Best regards.

Sergey Alexandrovich Kryukov 19-Mar-14 23:40pm

Great. Do you understand then how parametrized query prevents SQL injection through the user input? In this case, data is strictly isolated from the SQL statement...
—SA

AlwaysLearningNewStuff 19-Mar-14 23:47pm

I do not understand exactly how it is done, but I understand that this approach protects me the best. I still would like to limit the user to enter sane characters, not some nonsense like "-+Microsoft@.

To be honest, I believe that I have done the hard part just by using the parametrized query, the removal of invalid characters seem like "ice on the cake".

Thank you again. By the way that comic link is very refreshing solution! Haven't read it yet, but still funny and refreshing!

Best regards :)

Sergey Alexandrovich Kryukov 20-Mar-14 0:03am

You see, -+Microsoft@ does not have to be nonsense. Imagine that you have a columns (attribute) of some string type. Some people asked about their problem of entering some data string which was not interpreted correctly as a part of SQL string; so it required proper quoting, escaping of quotation and other characters, and so on. All such purely syntactic solutions are only relevant for manual input of SQL from some console. In the code, a parameter should be used. Roughly speaking, you can enter any string as a parameter, because it will never be attempted to be interpreted as a part of SQL; even though it could be any fragment of valid SQL, SQL with any number of syntax error, anything. SQL injection itself is based on the idea to enter some fragment of SQL, because it is not isolated from "real", intended SQL. With parameters, you guarantee to isolate data from syntax.
—SA

Sergey Alexandrovich Kryukov 20-Mar-14 0:18am

Validation, no validation... It's simpler: with concatenation, there is no an apparent boundary between data and SQL syntax. Even with validation. I would suggest, for any fixed previously known validation algorithm, it would be possible to create SQL injection bypassing such validation. By the way, it could make an interesting mathematical problem; how do you think?

Yes, with a parameter, it's always a string, a numeric fields, etc. One other important thing is performance: working with binary data immediately, not with strings representing data.

—SA

AlwaysLearningNewStuff 20-Mar-14 0:22am

Oops, I deleted the comment since there was no need for it anymore. As for the mathematical puzzle, it would be good thing. As soon as I get some free time I might try to do something similar.

Thanks again and best regards till next time!

Sergey Alexandrovich Kryukov 20-Mar-14 10:37am

My pleasure.
Good luck, call again.
—SA

AlwaysLearningNewStuff 20-Mar-14 0:20am

I have tried to perform SQL injection on a demo application and failed. After rereading your comment I now understand what you were saying about isolating data from SQL.

Just tell me this:
If I am safe this way, is there a point in limiting user input by restricting some characters?

Thank you.

Officially accepted and 5ed!

Best regards.

Sergey Alexandrovich Kryukov 20-Mar-14 0:26am

Maybe I would fail, too, for some pretty complex SQL...
Limiting user input? It could make sense in some application aspect, not from the point of view of safety, database integrity, etc.

For example, such table attributes as family name or given name should be a word starting with a capital letter. You might want to enforce that on input... or something like that. Telephone number format... whatever. But no need to check for anything which could be used for SQL injection...

—SA