|
As Wout Louwers has said, using SELECT TOP 1 will guarantee that only one row is returned. However, that is only for one query, and will not help if, somehow, an injection attack is occurring.
What you need to do is to make sure that that the account being used to access your SQL Server does not have any rights to query or modify the table(s). If the account that is accessing SQL Server has no rights to query or modify the table they cannot attack it. (By "query" I mean a SELECT statement, and by "modify" I mean INSERT, UPDATE or DELETE).
In order to actually check a users validity, or create a new user and so on, you will need to create stored procedures for the operations you wish to perform on the table. You then give the permission to EXECUTE the stored procedures to the accounts that you need.
The way SQL Server security works is that if the user attempts to execute a stored procedure then the SQL Server with check that user is authorised to do that. If so then they stored procedure can run and, normally, no further checks are made. This means that a user can be permitted to use a stored procedure that uses tables that the user does not have access to. This protects the tables as the stored procedure can make additional checks to ensure that the data is valid before the request is performed. It also means that only the operations you specify in the stored procedures can be carried out.
I said that "normally" the security is not checked again when the system discovers that the user has permission to execute the stored procedure. There are cases where additional checks are made. One example is if some dynamic SQL is executed inside a stored procedure. Dynamic SQL is where the stored procedure takes information from the parametes or from tables in the database and constructs a new query internally in a string, then executes the SQL in the string. Dynamic SQL is a source for secondary injection attacks. These are often more problematic to trace to the source because the source of the attack can lay dormant in the database for some time before becoming activated.
I hope I've managed to answer your question, and anticipate follow up questions. Let me know if you need more.
[EDIT]
Sorry, I just realised you were talking about MySQL and not SQL Server. The principles are more or less the same for all databases and should work if MySQL supports stored procedures - It was in the pipeline the last time I looked, but I don't know if it is available yet.
[/EDIT]
Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums
|
|
|
|
|
HA HA I LIED
Good day to you all and i hope you are well.
Not a direct code question guys, just in need of advice.
Imagine this scenario if you will,
You have a internet server with a SQL Server 2000 database and a client with a MSDE installation. Both are running the same database say MYDATABASE.
MYDATABASE on server is very large, say 100mb.
The client needs one table from the server to do the work they wish to do on their local machine.
They want to just get that one table (and all related obviously) updated on their machine as they don't need the full database as that would take ages (being 100mb).
How would you suggest passing that one table to the client, the client app updating the database on the local machine. Then, once they have done their work they re-connect to the internet server pass back the data they have added/updated and the server then updates accordingly.
Any help would be greatly appreciated.
My thoughts and ideas so far - DTS (not used this before though), dynamically formed SQL (clunky, unreliable).
Pleaaaaaseeee Help
Many Thanks
Harry
|
|
|
|
|
Please refrain from using that kind of title on this forum. If you expect for people to take your post seriously, please post it with a meaningful name.
~Javier Lozano
(blog)
|
|
|
|
|
I have installed Oracle 8i Release 3 (8.1.7)...I want to install now D2K ( Oracle forms 6i ) ...On inserting the installation CD...the "Oracle installer" screen appears ..then "Oracle installation settings" form appears ..Here we have to specify
Company Name:xyz
Oracle home details
Name: OraHome81
Location: C:\oracle\ora81
Language: English
With theses entries when I click OK button, the following error message with the Title "General Information" appears:
The location specified "C:\oracle\ora81", is already used as an Oracle Home for 'Oracle 817 Production'.
It can not be used as an Oracle home for 'nt'.
The OS am using is Windows 2000 Professional with
Pentium 4 ...Please help me out...Thanking you in advance.
|
|
|
|
|
Hello
I planned to genralize a stored procedure where i can pass the table name field names as parameter to the storedprocedure along with the values and then use those information to add or update the table which is passed as parameter. My stored procedure look like this
CREATE PROCEDURE Add_Edit_GSupportTables(@AddMode bit,@GenCode int,@GenName varchar(100),@GenTable varchar(50),@GenCodeField varchar(50),@GenNameField varchar(50)) as
declare @AutoCode numeric;
if @AddMode = 1
begin
select @AutoCode = max(@GenCodeField) from @GenTable
set @AutoCode = isnull(@AutoCode ,0)
set @AutoCode = @AutoCode +1
Insert Into @GenTable values(@AutoCode,@GenName)
end
else
begin
update @GenTable set @GenNameField =@GenName where @GenCodeField = @GenCode
end
GO
but it gives incorrect syntax near @GenTable
How to do so that this stored procedure can be used as functoin to add / edit
with regards
vimal
Help in need is the help indeed
|
|
|
|
|
I'd recommend splitting this into two procedures, add and edit. The query processor will either have to build one query plan with redundant code, or, more likely, recompile the query plan every time you swap the value of @AddMode.
As for your specific problem, you can only parameterize values. You can't parameterize schema. If you have to, you've probably designed your database incorrectly. Tables are not objects. Creating and dropping tables are very expensive operations.
If you absolutely must do this, you should use string concatenation to form the schema part of the query, then use sp_executesql to execute it supplying the parameters, e.g.:
DECLARE @query varchar(2000)
SET @query = 'UPDATE ' + @GenTable +
' SET ' + @GenNameField + ' = @GenName WHERE ' +
@GenCodeField + ' = @GenCode'
EXEC sp_executesql @query,
N'@GenName varchar(100), @GenCode int',
@GenName, @GenCode
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Example:
Create procedure prg.ExamleProcedure
@tabloname varchar(50),
@data int
as
declare @stmt varchar(8000)
select @stmt = 'insert into ' + @tabloname + ' (column1, column2) ' +
'select ' + @data + ' , NULL'
Exec (@stmt)
You can solve your problem like this.
abbaskaya
|
|
|
|
|
Example:
Create procedure prg.ExamleProcedure
@tabloname varchar(50),
@data int
as
declare @stmt varchar(8000)
select @stmt = 'insert into ' + @tabloname + ' (column1, column2) ' +
'select ' + @data + ' , NULL'
Exec (@stmt)
You can solve your problem![Rose | [Rose]](https://codeproject.freetls.fastly.net/script/Forums/Images/rose.gif) like this.
abbaskaya
|
|
|
|
|
Thanks Mr Abbaskaya and Mr Mike
I have 8 tables in my database which have the same structure and so i planned to do a general function so that i can make those 8 modules as a single functions and can use parameters to make it work.
I have tried the way you have explained above but i am getting a syntax error.Could you tell me exactly how to implement that with correct syntax so that i can check it here.
Thanks a lot for your help
with regards
vimal
Help in need is the help indeed
|
|
|
|
|
If you have 8 tables with the same structure you should put them all in one table and drop the seven other ones. That would save your problem.
Wout Louwers
|
|
|
|
|
those 8 tables are same structured but due to simplicity and future oriented modifications and additions i am in a position to use 8 different tables.
with regards
vimal
Help in need is the help indeed
|
|
|
|
|
What do you mean with 'due to simplicity'. If the use of the 8 tables was that simple you wouldn't have to ask this question.;)
due to simplicity and future oriented modifications and additions i am in a position to use 8 different tables.
You sound like a manager (PHB). Not like a programmer.
Wout Louwers
|
|
|
|
|
Unable to install Oracle 8i Release 3 (8.1.7) on Windows 2000 Professional . When I insert the installation CD..the Oracle Enterprise Edition - Autorun screen appears..then on clicking on the "Install/Deinstall Products" option ...some processing occurs..and then no further steps appears...According to what I have heard,
Oracle 8i Release 3 (8.1.7) gets easily installed on Windows 2000 Professional and has some problems with XP ,then where the problem lies...Can someone one help me out...Thanking you in advance.
|
|
|
|
|
Do you have a Pentium 4 processor? If so, see here[^], especially the post from Bart-Jan Keetels.
|
|
|
|
|
Thank u ....it worked!!
|
|
|
|
|
With ref. to ASP.NET web application:
suppose there are two textbox (web form controls) , one reset button(HTML control) on the screen.
when the page is loaded for the first time,I enter some values in two textboxes and press reset button.
Values from the text boxes gets cleared on clicking the reset button. If I perform the same activity again reset button doesn't work.
|
|
|
|
|
|
I have windows 2000 professional edition installed on my system.
When I try to open a new project in ASp .NET web applications or web services, I get the following error:
"Visual Studio .NET has detected that the specified webserver is not running ASP.NET version 1.1. You will be unable to run ASP.NET web applications or
services."
Note that Webserver is running propery on my PC.
How to resolve this error?
|
|
|
|
|
This refers to the records returned by the "For XML auto" in ADO.Net.
The first case is simply retirning results from a select statement from a
table returning some columns with for xml auto.
--------------------------------------------
[No erros, results returned]
Database2, connection2
SqlConnection1.Open()<br />
Dim SQLCmd As New SqlClient.SqlCommand<br />
SQLCmd.Connection = SqlConnection1<br />
SQLCmd.CommandType = CommandType.Text<br />
SQLCmd.CommandTimeout = 20<br />
SQLCmd.CommandText = "SELECT CustomerID, CompanyName, City, PostalCode, Phone FROM Customers FOR xml auto"<br />
Dim myXmlReader As System.Xml.XmlReader = SQLCmd.ExecuteXmlReader()<br />
<br />
'myXmlReader.MoveToContent()<br />
'myXmlReader.MoveToElement()<br />
Response.Write("<table border='1'>")<br />
<br />
While myXmlReader.Read<br />
'Response.Write(myXmlReader.Item("CustomerID") & "<br>")<br />
Response.Write("<tr><td>" & myXmlReader.Item("CustomerID") & " " & myXmlReader.Item("CompanyName") & _<br />
myXmlReader.Item("City") & "" & myXmlReader.Item("PostalCode") & "</td></tr>")<br />
End While<br />
Response.Write("</table>")<br />
myXmlReader.Close()<br />
SqlConnection1.Close()<br />
[No errors, results returned]
-----------------------
The second case is, results expected to be returned from table joins using For xml auto. In this case no results were returned, no errors or exceptions, exactly same trace info as in the first case
[No errors, no output(empty table), trace info exactly same as the one with results returned]
Database1, connection string1
SqlConnection1.Open()<br />
Dim SQLCmd As New SqlClient.SqlCommand<br />
SQLCmd.Connection = SqlConnection1<br />
SQLCmd.CommandType = CommandType.Text<br />
SQLCmd.CommandTimeout = 20<br />
SQLCmd.CommandText = "SELECT nums.NumId, nums.Nums, color.colr FROM color INNER JOIN nums ON color.numid = nums.NumId FOR xml auto, ELEMENTS"<br />
'SELECT nums.NumId, nums.Nums, color.colr FROM color INNER JOIN nums ON color.numid = nums.NumId FOR xml auto<br />
'SELECT CustomerID, CompanyName, City, PostalCode, Phone FROM Customers FOR xml auto<br />
Dim myXmlReader As System.Xml.XmlReader = SQLCmd.ExecuteXmlReader()<br />
Response.Write("<table border='1'>")<br />
<br />
While myXmlReader.Read<br />
'Response.Write(myXmlReader.Item("CustomerID") & "<br>")<br />
<br />
Response.Write("<tr><td>" & myXmlReader.Item("nums.NumId") & " " & myXmlReader.Item("nums.Nums") & _<br />
myXmlReader.Item("color.colr") & "</td></tr>")<br />
End While<br />
Response.Write("</table>")<br />
myXmlReader.Close()<br />
SqlConnection1.Close()<br />
[No errors, no output]
What could be different?
|
|
|
|
|
Possibly the join yeilds no matches. Try the query in Query analyzer (first as a normal query, - no forXml, then just without the ELEMENTS directive).
Why would anyone waste time arguing with an accountant about anything? Their sole function is to record what happenned, and any higher aspirations are mere delusions of grandeur. On the ladder of productive contributions they are the little rubber pads at the bottom that keep the thing from sliding out from under you. - Roger Wright
|
|
|
|
|
That would be the first thing I would try. It does return resutls in Query Analyzer.
Thanks,
Happy New Year
Mysorian
|
|
|
|
|
how to make a case sensitive comparsion in sql ?
select * from users where Password = 'ahmed'
is the same
select * from users where Password = 'AhmEd'
how to make it case-sensitive ?
|
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
