Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nice123ej
Starting Member
48 Posts |
 Posted - 2009-11-23 : 22:43:07
|
| HiI am using dynamix sql EXECUTE('.....')to avoid injection i stopped users from using words like SELECT, INSERT, DELETE, UPDATE, DROP, CREATE, ALTER, GRANT.is this enough?can user still user EXECUTE inside the EXECUTEis there any way i can force sql script to do 1 statement on 1 table only like (this EXECUTE will only UPDATE table myTable)thanks |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
nice123ej
Starting Member
48 Posts |
Posted - 2009-11-24 : 00:09:57
|
| parameterized query does not fit my scenariosI built UPDATE statment in stored procedure, it is complex update.anyway, will avoiding these SELECT, INSERT, DELETE, UPDATE, DROP, CREATE, ALTER, GRANT will keep me safe?i was thinking what if user used something like EXEC(REPLACE('D:E:L:E:T:E FROM myTable', ':', '')) in this case i wont be able to catch it :(any idea how to protect my tables? |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
nice123ej
Starting Member
48 Posts |
Posted - 2009-11-24 : 00:35:29
|
| In the system we allow user to creat thier own fieldsso in the update statement, column name as actually records in another table and column values are another recordsUserCustomTableRecName RecValue--------- --------CarReg 'ABC 123'CarYear 2007CarExpiry CarYear + 1so I create variable table @myTable that has columns from UserCustomTable, and using cursor i build the update statementto get the final resultsin the end my update will look like this UPDATE @myTable SET CarReg = 'ABC 123',CarYear = 2007,CarExpiry = CarYear + 1so if user typed something like DELETE FROM UsersTable instead of 'ABC 123' then a big trouble. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
nice123ej
Starting Member
48 Posts |
Posted - 2009-11-24 : 17:39:37
|
| Thanks for the advice. we will change the design in the next project.for now does anyone know any way i can protect the current design database from SQL injection?Thanks |
|
|
|
|
|
|
|
|
dynamic sql and injection