?
Solved

Hacking and Sql parameters

Posted on 2005-03-18
11
Medium Priority
?
412 Views
Last Modified: 2010-04-16
Hey all,

I read numerous times and I think it is well known that you should never ever do something like this in your code:

string sql = "SELECT * FROM MyTable WHERE MyColumn = '" + parameter + "'";
SqlCommand cmd = new SqlCommand(sql);

where 'parameter' is for example some value from an input field.

However, I'm looking at some old Database code here for a company and sometimes SQL queries are done this way. The only thing done with the 'paramter' value is checking for single / double quotes, null values, dates, etc, so a user will never get an ugly exception when trying to insert some invalid value with quotes or a date in a wrong format, etc.

My question is, is this still very unsafe? And if so, can someone please explain why? I'm not looking for examples of malicious values, but something more detailed than 'it is unsafe' is appreciated. Are we talking about buffer overflows or something? Is this problem fixed in nowadays versions or is it still a vulnerability? Does it *absolutely* have to be converted and done with the SqlParameter way? If someone can answer this for me, it would be appreciated.

Thanks :)
0
Comment
Question by:Razzie_
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +3
11 Comments
 
LVL 14

Accepted Solution

by:
Thandava Vallepalli earned 450 total points
ID: 13572901
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 13572934
for safe side replace single quotes with 2 single quotes...

==================================
parameter = parameter.Replace("'", "''");
string sql = "SELECT * FROM MyTable WHERE MyColumn = '" + parameter + "'";
SqlCommand cmd = new SqlCommand(sql);
==================================

itsvtk
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Assisted Solution

by:NipNFriar_Tuck
NipNFriar_Tuck earned 150 total points
ID: 13576413
Razz,

I am sure those links give you a complete understanding of the issue... Here is the short explanation: A malicious user could but in a string that would case the buffer over run then place a malicious application an your server that would... say send all passwords back to them.  They could then take this information hack into you network and... well you get the point.

HTH.
0
 
LVL 23

Assisted Solution

by:b1xml2
b1xml2 earned 150 total points
ID: 13581985
1. URL: http://www.4guysfromrolla.com/webtech/061902-1.shtml is really relevant in ASP and NOT ASP.NET.
2. We should not be using "SELECT * FROM MyTable WHERE MyColumn = '" + parameter + "'"; at all within the .NET Framework.
3.
The Code>>>
parameter = parameter.Replace("'", "''");
string sql = "SELECT * FROM MyTable WHERE MyColumn = '" + parameter + "'";
<<< Does NOT protect you from SQL Injection Attacks

Example Of Using SqlParameters
=====================

string sql = "SELECT * FROM MyTable WHERE MyColumn = @Par";
SqlCommand cmd = new SqlCommand(sql);
cmd.Parameters.Add("@Par",SqlDbType.VarChar).Value = "somthing";
0
 
LVL 23

Expert Comment

by:b1xml2
ID: 13581989
The Use Of Parameters will protect against SQL Injections and removes the need for replacing single quotes with double quotes.
0
 
LVL 8

Author Comment

by:Razzie_
ID: 13583961
Thanks all so far.

One more thing - b1xml2... you say '<<< Does NOT protect you from SQL Injection Attacks'. But I can't read that anywhere nor find an explanation. On the contrary, so far everything I've read says that replacing single with double quotes is good protection. So can you clarify?

(replacing single quotes with double quotes already happened in the code I reviewed)
0
 
LVL 10

Expert Comment

by:ptmcomp
ID: 13586320
I strongly agree with b1xml2
0
 
LVL 8

Author Comment

by:Razzie_
ID: 13769864
Sorry for the slow reaction from my side, been too busy :)
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question