Re: SQL Injection - Database Discussion Boards

Re: SQL Injection

Ritesh123410-Jan-08 4:27

Ritesh1234

10-Jan-08 4:27

there there is no way to inject after replacing ' with '' Sniff | :^)

Re: SQL Injection

Colin Angus Mackay12-Jan-08 1:32

Colin Angus Mackay

12-Jan-08 1:32

What about injecting into values that don't need quotes around them?

Upcoming FREE developer events:
* Developer Day Scotland

Recent blog posts:
* Follow up on hiring a software developer
* The Value of Smaller Methods

My website | blog

Re: SQL Injection

Dave-B10-Jan-08 5:31

Dave-B

10-Jan-08 5:31

This Page
http://www.sommarskog.se/dynamic_sql.html[^]

Contains a lot of info about sql injection

Re: SQL Injection

Ritesh123410-Jan-08 9:07

Ritesh1234

10-Jan-08 9:07

thankx for link.I went through this but still could not got my answer.

Can u pls help me out to find in what way this query can venerable to SQL injection

strQuery = "select * from Table where Name ='" & strName.Replace("'","''") & "'"

Re: SQL Injection

Pete O'Hanlon10-Jan-08 9:55

Pete O'Hanlon

10-Jan-08 9:55

Why are you looking to do this? It's much better to use parameters which take care of these things for you and are a much better way of preventing SQL Injection attacks. Please read this[^] article and do yourself a favour.

Deja View - the feeling that you've seen this post before.

My blog | My articles

Re: SQL Injection

Dave Kreskowiak10-Jan-08 10:04

Dave Kreskowiak

10-Jan-08 10:04

Ritesh1234 wrote:
Can u pls help me out to find in what way this query can venerable to SQL injection

Yes, it's STILL an injection attack, and a rather successful one if the code that depends on this query doesn't expect to find 0 results comming back. The replacement of ' with '' is NOT a guarantee against injection attacks, and neither is using parameterized queries, though using parameters and the SqlParameter objects does look for other possible problems that you don't normally think of, such as DateTime representation in the SQL statement.

Simply put, there is no reason NOT to use parameterized queries and stored procedures. It makes you code much more robust, easier to debug, and easier to support when it breaks, not if. It's also no excuse for not thoroughly checking user input before you pass it to SQL, which is what you're code snippet is suggesting you're not doing. Consider ALL user input as evil. It MUST go through validation testing before you try to use it.

What if the user typed in 1000+ characters into that textbox?? What happens when you pass that to your SQL, which is only expecting, maybe, 14 characters??

What you have is a lazy way of attempting to secure your SQL code without understanding what an SQL Injection attack really is. Make no mistake, your "solution" is not secure, not in the least.

Read this[^] or Colin will make you read it.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

Re: SQL Injection

Ritesh123411-Jan-08 3:14

Ritesh1234

11-Jan-08 3:14

thanks buddy for u r valuable input well first of all this is NOT my way coding and i raised this question just to find out any good EXAMPLE how attacker can take advantage of this poorly fabricated query.Though we all advocating parameterized queries and stored procedures including ME and even this query seems easily attackable but still could not figured out HOW neither got any single example from anyone Sniff | :^)

btw that was the first article which make me aware of the SQL injection long ago Blush | :O

Re: SQL Injection

Dave Kreskowiak11-Jan-08 6:42

Dave Kreskowiak

11-Jan-08 6:42

"The Six Dumbest Ideas in Computer Security[^]" is one of the best essays I've seen on security. Make sure you pay attention to point #2.

How many different ways are there to hack a database?? There are dozens and dozens of them. Now add the poor security in your code and you've opened up dozens more. Are you going to address each one of these vulnerabilities on an individual basis, such as that one Replace statement?? How about the other 9,999 vulnerabilities?? Starting to see the point behind "Enumerating Badness"??

If you read the entire article, it explains perfectly why the mere existance of virus scanning software is a stupid idea. And it's one which I happen to subscribe to.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

MySql C# Stored Function Problem

js800859-Jan-08 6:34

js80085

9-Jan-08 6:34

I am writing a class to talk to a database I've written, the database has a stored function which takes one parameter and returns a value, it works fine when I execute it just with MySql however when I use the class I have written I get no error but the value that is returned from "ExecuteScalar" is null. The executeNoReturn Works perfectly for a stored procedure that adds some data to the DB.

Any suggestions would be appreciated

public class Parameter
{
private string mName;
private object mValue;

public Parameter(string nameIn, object valueIn)
{
name = nameIn;
value = valueIn;
}

public string name
{
get
{
return mName;
}
set
{
mName = value;
}
}

public object value
{
get
{
return mValue;
}
set
{
mValue = value;
}
}
}
public class Db
{
MySqlConnection conn;
public Db()
{
conn = new MySqlConnection("Database=test;Data Source=localhost;User Id=root;Password=alexa");
conn.Open();
}

public object executeSingleReturn(string storedName, List<parameter> parameters)
{
object returnObject = new object();
MySqlCommand command = new MySqlCommand();

command.Connection = conn;

command.CommandType = System.Data.CommandType.StoredProcedure;

command.CommandText = storedName;

foreach (Parameter a in parameters)
{
command.Parameters.AddWithValue(a.name, a.value);
}

returnObject = command.ExecuteScalar();

return returnObject;
}

public void executeNoReturn(string storedName, List<parameter> parameters)
{
MySqlCommand command = new MySqlCommand();
MySqlTransaction trans;

trans = conn.BeginTransaction();
command.Connection = conn;
command.Transaction = trans;

command.CommandType = System.Data.CommandType.StoredProcedure;

command.CommandText = storedName;

foreach (Parametera in parameters)
{
command.Parameters.AddWithValue(a.name, a.value);
}

command.UpdatedRowSource = System.Data.UpdateRowSource.None;

command.ExecuteNonQuery();

trans.Commit();
}

~Db()
{
conn.Close();
}
}

Help in query design

www.Developerof.NET9-Jan-08 5:35

www.Developerof.NET

9-Jan-08 5:35

Hi all,

I need some help in query design...Following is the scenerio

table1

|length|breadth|height|dimen|
|20|20|20|20x20x20|
|40|20|20|40x20x20|
|50|50|20|50x50x20|
|20|20|70|20x20x70|

the user in stage 1 of the app inserts only the len,bre,height... in cycle 2 i have to calculate the dimen(lenxbredxheight).How can i do it using a single query..Do i need to use cursors ..If yes then how????

Thanks in adv...

When you fail to plan, you are planning to fail.

Re: Help in query design

DotNetXenon9-Jan-08 7:58

DotNetXenon

9-Jan-08 7:58

Would this not be enough? Why do you think you need to use Cursors?

update table1
set dimen = length*breadth*height

------------------------------------------------------------
"The only true wisdom is in knowing you know nothing." --Socrates

Re: Help in query design

GuyThiebaut9-Jan-08 10:06

GuyThiebaut

9-Jan-08 10:06

Try this in stage 2:

<br />
update table1<br />
set dimen = cast(length as varchar(2)) + 'x' + cast(breadth as varchar(2)) + 'x' + cast(height as varchar(2))<br />
from table1

Regards

Guy

You always pass failure on the way to success.

Re: Help in query design

veereshIndia13-Jan-08 19:46

veereshIndia

13-Jan-08 19:46

Hi,

While you inserting the first three data i.e length,breadth,height do like
this

insert into table1(length,breadth,height,dimen)values
(length,breadth,height,length*breadth*height)

Or

First insert

insert into table1(length,breadth,heightvalues
(length,breadth,height)

in the second cycle just update the table.

Regard's
Veeresh

i want to join this group

ASP.Net: Custom control problem with XML file data source

Emma Burrows9-Jan-08 5:35

Emma Burrows

9-Jan-08 5:35

Hello everybody

I finally have a bit of time to look into a problem which has been annoying me for some months, without a solution so far. I'm hoping some clever guru will take pity on me and help resolve it. Smile | :)

The situation:
I have created a custom control that uses a data source to display dates on a web site, and allow the user to select a range of dates.

However, I have different problems depending on the data source I use.

- Access database using a dataset generated in VS 2005: selecting a range works beautifully

However, I then can't overwrite the Access file on my ISP's web server, and besides, it's overkill for a handful of records, so I tried solution number 2:

- XML file loaded into a dataset created in my code: the control "forgets" the data source when the user selects a range.

The control in its buggy XML incarnation is visible here: http://www.eburrows.co.uk/apt111/availabilitybug.aspx.

While its working Access dataset-based version is here:
http://www.eburrows.co.uk/apt111/availability.aspx

Of course, full source code will be provided to anyone kind enough to volunteer their expertise! Smile | :)

Thanks!

da.Fill(second datatable)

CandyMe9-Jan-08 5:20

CandyMe

9-Jan-08 5:20

Hi! Just a basic question but I don't know the answer =)
Please help

So I have a stored procedure with 2 select query
SELECT A,B FROM table1
SEleCT C,D FROM table2

In my typed dataset, how do I retrieve values in table2 using da.Fill() or should I be using another code for this?

da.Fill(dt);
when I can't use somethign like da.Fill(dt2);

Thank you very much.

Gerri

Re: da.Fill(second datatable)

Tom Deketelaere9-Jan-08 5:25

Tom Deketelaere

9-Jan-08 5:25

you need to fill a dataset with the output from the stored procedure

so:
dim ds as dataset
da.fill(ds)

then the dataset will have 2 tables in it (normally)

and you can select the 2 tables by there index

dim dt1 as datatable = ds.tables(0)
dim dt2 as datatable = ds.tables(1)

sorry for the vb.net code but should be easly translated

hope this helps

If my help was helpfull let me know, if not let me know why.

The only way we learn is by making mistakes.

Re: da.Fill(second datatable)

CandyMe9-Jan-08 5:32

CandyMe

9-Jan-08 5:32

Thank you. I would just like to ask. I have a Report.xsd file and uses this:

DataAccess.dsReportManagementTableAdapters.rpt_RetrieveTableAdapter da = new DataAccess.dsReportManagementTableAdapters.rpt_RetrieveTableAdapter();
DataAccess.dsReportManagement.rpt_RetrieveDataTable dt = new DataAccess.dsReportManagement.rpt_RetrieveDataTable();
da.Fill(dt);

How come my Fill() doesn't take a DataSet as a parameter?

Thank you.

Gerri

Re: da.Fill(second datatable)

Tom Deketelaere9-Jan-08 7:34

Tom Deketelaere

9-Jan-08 7:34

because you don't use a dataadapter but a tableadapter
from the looks of it you are trying to extract data from a xml file (on a side note xml isn't the ideal way for datastorage) I have never tryed this before but look of you cant find a xmldataadapter (or somthing like that) normally that should accept a dataset as parameter

If my help was helpfull let me know, if not let me know why.

The only way we learn is by making mistakes.

insert special characters into sql database

swissmiss869-Jan-08 5:02

swissmiss86

9-Jan-08 5:02

Please assist me in inserting special characters into a sql table whose column datatype is set to varchar. I actually developed a VB script which extracts data from xml file and inserts into sql database. In the xml file i have data where description of a field has text like phone ® (i.e text representation of registered trademark symbol). This is causing error when parsing the xml file and it says ERROR: Reference to undefined entity 'reg'. Any help would be appreciated.

Re: insert special characters into sql database

Ritesh12349-Jan-08 12:54

Ritesh1234

9-Jan-08 12:54

why don't u change column DB type to nvarchar !! Sniff | :^)

force table names [modified] admin please move post to vb.net forum (thank you)

Tom Deketelaere9-Jan-08 3:47

Tom Deketelaere

9-Jan-08 3:47

I have a sql statement wich gets the columnnames from several table by simply selecting nothing and putting that into a datatable (vb.net) off wich I read the columns
it works fine with one exception
when I have the same columnname more than once an number is added behind the column name
for instance if I have the columnname 'country' twice I will get 'country' and 'country1'
is there anyway to force it to display 'project_country' and 'client_country' or something like that so that I know from wich table the column is comming.

I use select * so giving the columns a unique name in the select statement is impossible. I use the select * because there is the posibilaty that a column is added after deployment of the main programme. Not using the select * would mean I would have to alter the sqlstatement to include this column and that would defeat the whole purpose of the programme I'm currently creating.

any help would be appriciated

after further investigation I found out that this is a vb.net problem so could an admin please move this post there, thank you

If my help was helpfull let me know, if not let me know why.

The only way we learn is by making mistakes.

modified on Wednesday, January 09, 2008 10:37:55 AM

Calculation not done in SubQuery :(

ha_haseebahmad9-Jan-08 0:20

ha_haseebahmad

9-Jan-08 0:20

Follows are the query which returns Budget, FortheMonth, YeartoDate, ProjectToDate column through subQuery....Which i use column in subQuery it will not sum/Calculate the amount when i hard code values which are in VW_FN_ANASTUDYSETUP.RootIDs column then it calculate....please guide me in my query...thanks

SELECT ANA_ID, RootIDs, ANA_PARENT_ID, DESCRIPTION, BUD, FORTHEMONTH, YEARTODATE, PRJTODATE, ISNULL(BUD, 0) - ISNULL(PRJTODATE, 0) AS BLCOMP
FROM (SELECT ANA_ID, RootIDs, ANA_PARENT_ID, DESCRIPTION,
(SELECT VW_FN_ANASTUDYSETUP.Formula * SUM(b.bamnt) AS BUDGET
FROM FN_ANASTUDYSETUPLINEITEMS a INNER JOIN
VW_FN_gldbud b ON a.MAINCODE = b.MAIN AND a.DETAILCODE = b.Detail AND a.CCTRCODE = b.CCTR
WHERE (a.ANA_ID IN (VW_FN_ANASTUDYSETUP.RootIDs))) AS BUD,
(SELECT VW_FN_ANASTUDYSETUP.Formula * SUM(CASE WHEN b.credit = 1 THEN - b.glamnt ELSE b.glamnt END) AS FORTHEMONTH
FROM FN_ANASTUDYSETUPLINEITEMS a INNER JOIN
VW_FN_glacnt b ON a.MAINCODE = b.MAIN AND a.DETAILCODE = b.Detail AND a.CCTRCODE = b.CCTR
WHERE (a.ANA_ID IN (VW_FN_ANASTUDYSETUP.RootIDs))) AS FORTHEMONTH,
(SELECT VW_FN_ANASTUDYSETUP.Formula * SUM(CASE WHEN b.credit = 1 THEN - b.glamnt ELSE b.glamnt END) AS YEARTODATE
FROM FN_ANASTUDYSETUPLINEITEMS a INNER JOIN
VW_FN_glacnt b ON a.MAINCODE = b.MAIN AND a.DETAILCODE = b.Detail AND a.CCTRCODE = b.CCTR
WHERE (a.ANA_ID IN (VW_FN_ANASTUDYSETUP.RootIDs))) AS YEARTODATE,
(SELECT VW_FN_ANASTUDYSETUP.Formula * SUM(CASE WHEN b.credit = 1 THEN - b.glamnt ELSE b.glamnt END) AS PRJTODATE
FROM FN_ANASTUDYSETUPLINEITEMS a INNER JOIN
VW_FN_glacnt b ON a.MAINCODE = b.MAIN AND a.DETAILCODE = b.Detail AND a.CCTRCODE = b.CCTR
WHERE (a.ANA_ID IN (VW_FN_ANASTUDYSETUP.RootIDs))) AS PRJTODATE
--(a.ANA_ID IN (0000000012,0000000013,0000000014,0000000015))) AS PRJTODATE

FROM VW_FN_ANASTUDYSETUP
WHERE (LEVEL_ID = '3') AND (BA_ID = '5')) Q_Main

how to define a result a in a sp

jagan1238-Jan-08 23:21

jagan123

8-Jan-08 23:21

how to define a result for accessing the select statement from the sp;