I am a JAVA programmer. And I have an experience to share, thought could help someone. You know, in Java, there’s one stuff called PreparedStatment, I don’t know whether .NET has it or not.
Besides, you could filter some sql keywords in enter parameters, but this method is not that advanced, if filtering like this, then it would not work out if normal business has these keywords.
Sql injection is in the SQL sentence with ulterior motives, such as:
insert into table (id,content) values ((SELECT 1 FROM USER WHERE ROWNUM <2),'xxxx')
select * from table where id=1 OR 1=1
select * from table where name='XX' OR '1'='1'
As for the case above, we’d have to ensure two things to defend SQL injection:
1. As for numbers, you’d have to ensure it’s number, but not “1 or 1=1”
2. As for character strings, just need transferred meaning inverted comma, then become to elect * from table whee name='XX'' OR ''1''=''1' (I don’t know some database whether need filter double quotation marks).
As the efficiency to judge whether a parameter includes number only is not that high, where id=1 could be changed as where id=’1’. Then you just need to transfer inverted comma for all parameters.
Add inverted comma for all parameters, but not consider numbers or character strings. Then it’s OK. It’s also a good habit, as some database design might set ID type as character string. If like this, id=1 would not use index for the field id.
The above is just my experience, the database is base