0

I wanted to know if there is any other measures against SQL injection that can be taken apart from using parametrized Query and validating data. Thanks!

Stefano Mtangoo
  • 6,017
  • 6
  • 47
  • 93
  • an accepted answer along with important addition from mine [Best way to stop SQL Injection in PHP](http://stackoverflow.com/questions/60174/best-way-to-stop-sql-injection-in-php) covers it all. – Your Common Sense Dec 20 '11 at 20:34

3 Answers3

0

Obviously ensure you are validating data on the server-side in addition to anything you may be doing on the client.

Also, if you're talking web make sure you are validating all data, i.e. QueryString's and Cookie Value's as well as Form fields.

I know this is the first hit on Google but I do read this article over from time to time and really rate it (again relates to web): http://www.securiteam.com/securityreviews/5DP0N1P76E.html

Barry Kaye
  • 7,682
  • 6
  • 42
  • 64
  • But isn't that validation issue? I wanted to know if there is more than validation and prepared statements. Thanks for comment and I will read the link (seems good from first look) – Stefano Mtangoo Jul 23 '11 at 15:40
0

I always run my user input text through a custom sanitiser server side, so I can strip out all the nasty stuff in case it gets through. (& " = ' etc)

I don't have any SQL statements in my code except for the stored procedures I call, so even if they do find a vulnerability they'll have to figure out my stored procedure before even touching the tables.

In the stored procedure parameters you can limit the text sizes, for example VARCHAR(10) so if you're normally expecting string "123456" and "12345' AND UNION SELECT * FROM MEMBERS INNER JOIN MEMBER_ADDRESS ON ID" comes through, the stored procedure won't like it.

Also one last point, try to catch all exceptions that come back, and try to handle them gracefully. Sometimes you see websites display something like "could not connect to database, 'USER_ID' does not exist in mydatabase.member". Giving someone a sniff at the architecture of your database will start the ball rolling for an exploit.

tutts
  • 2,373
  • 2
  • 20
  • 24
  • Thanks. May I ask you a question? I know I can Google but just getting someone explaining is better. How do you do it without single SQL statement? How do you make stored procedures? Thanks! – Stefano Mtangoo Jul 23 '11 at 20:21
  • Sure, although it depends on what sql database you have. If like most people you're on a linux server, you will have MySQL (5.X.X) by default - which is my personal preference. A stored procedure, is basically what it says, a procedure (like a method) thats stored in the database. You can create a SP using something like this (execute SQL direct in DB): CREATE PROCEDURE `my_db`.`get_my_user` (in _id INT(10)) BEGIN SELECT * from test where My_ID = _id; END Then in your chosen language call that procedure by executing this SQL statement instead: sql = "call get_my_user($value)"; – tutts Jul 25 '11 at 12:39
  • isn't call `call get_my_user($value)` being a regular SQL query and thus suffer from the same vulnerabilities? – Your Common Sense Dec 21 '11 at 06:06
  • depends whether the stored procedure concatenates the user input into an SQL query and then runs it, you should use prepared statements to minimise the risks of injection. – tutts Dec 21 '11 at 08:48
  • also... @Col. Shrapnel I suggest you do your research on your question next time before down voting on a whim. – tutts Dec 21 '11 at 09:08
  • if I have to use prepared statements anyway, what's the profit from using stored procedures in terms of injection protection? – Your Common Sense Dec 21 '11 at 09:11
0

With All good answers above, What I did is create a script that scans all tables and creates whitelist for table names and columns then I use that to validate any user input that is supposed to be table/column name since they don't go into parametric query. Anything else is parameterized via PDO Bind!

Stefano Mtangoo
  • 6,017
  • 6
  • 47
  • 93