Database

Re: Stored procedure help

27-Jun-09 17:13

Depending on your deployment, they are generally stored either on MSDB database or in the file systems.

I basically use VS 2008 and MSSQL2005 and the SSIS packages are stored in

drive name:\Documents and Settings\My Documents\Visual Studio 2005\Projects

Or else you can also look into thru the defined projects path in Tools>Options of Visual Studio

Anyway, for more details on

How to: Save a Package to the File System visit this link
[ http://msdn.microsoft.com/en-us/library/ms137970.aspx[^] ]

How to: Save a Copy of a Package in MSDB database

[ http://msdn.microsoft.com/en-us/library/ms137565.aspx[^]]

Hope this helps
Smile | :)

Niladri Biswas

Stored procedure help

paypony27-Jun-09 1:34

paypony

27-Jun-09 1:34

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER proc [dbo].[Usp_D_et_Login1234]
@name nvarchar(max),
@pwd nvarchar(100),
@type nvarchar(5)

as
begin
if @type='1'
begin
select DId,LoginName from dd_login where LoginName=@name and Password=@pwd
end

if @type='0'
begin
select RId,LoginName from dr_login where LoginName=@name and Password=@pwd
end

end

Is this stored procedure can be injected?
Whether sql injections can be apply on this procedure?
If yes tell me how ?
and also how to recover from it? In sqlserver2005.(backend)(asp.net frontend)

Niladri_Biswas27-Jun-09 6:05

Fetching dependent records from the same table

27-Jun-09 6:05

Hi man,

I would like to shed some light on this topic.

As far as I know, SOL injection happens in the parameters that we are sending to the stored proc and not inside the stored proc.

And the most venerable datatypes are the strings.

It is always recommended to put a check on the string data types.

However, the risk of sql attack can be minimised if you are using the

SQLCommand and SQLParameter ADO/ADO.NET objects.

For more knowledge, I recommend you to go through these tutorials

1) http://msdn.microsoft.com/en-us/library/ms998271.aspx[^]

2)http://www.devcity.net/Articles/250/1/article.aspx[^]

Regarding your question as how to protect injection from asp.net, besure about one thing, that using javascript, sql injection is highly possible.

Look into this article

http://kb2.adobe.com/cps/581/581a553c.html[^]

For a precise example that you posted here, a good answer can be found here
http://www.sitepoint.com/article/sql-injection-attacks-safe/2/[^]

Hope this helps.
Smile | :)

Niladri Biswas

Neeraj Kr26-Jun-09 7:12

Neeraj Kr

26-Jun-09 7:12

Hello, I am new to databases. I need urgent help. I have a table named Category, which contains 4 fields - categoryid, siteproprtyid, topCategoryId.

the topcategoryid contains the categoryid of the higher level category. Likewise there are four level of category. An example to this is-

categoryid sitepropertyid topcategoryid
---------- -------------- -------------
1 16 Null
2 16 1
3 16 2
4 16 3
5 16 1

I want the output to be like

Category1 Category2 Category3 Category4
--------- --------- --------- ---------
1 2 3 4
1 5

Please help me regarding this

Re: Fetching dependent records from the same table [modified]

Niladri_Biswas26-Jun-09 15:54

26-Jun-09 15:54

Hi, your question is not so clear.

As of now what I understood, you need a row column transposition which can be achieved using PIVOT function of SQL SERVER .

You are saying that there are 4 level of categories.

In the output, that you presented here, I cannot find any sort of link between the first and second rows.

What is 5 doing in the second row? How is it related in this context? Does 1 will always appear in the Category1?

Is this 1, the TOPCATEGORYID and is it a demarcation indicating for a next row?

Category1 Category2 Category3 Category4
--------- --------- --------- ---------
1 2 3 4
1 5

Explain everything clearly... then it will be easier to answer your problem.

Smile | :)

Niladri Biswas

modified on Friday, June 26, 2009 10:14 PM

Neeraj Kr26-Jun-09 19:45

Neeraj Kr

26-Jun-09 19:45

Hi, yea may be I was not able to put my question properly. Let me try once more. Suppose CategoryId 1 is the root level, so thete is no top level category for it and it is null. The CategoryId 2 is the child of CategoryId 1, therefore, the topcategoryid for Category 2 is 1. Like wise for Category 3, the topcategoryid is 2 and for CategoryId 4, the topcategoryid is 3. CategoryId 5 is the child of CategoryId 1 and there are further no child categories under it.

Therefore, I showed thebase table like that. For the same, I want the output in the manner I depicted.

I hope I am able to explain this time.

Thanks for bearing with me.

-----Have A Nice Day-----

Mycroft Holmes27-Jun-09 0:37

Mycroft Holmes

27-Jun-09 0:37

If you are using SQL Server 2008 then look into the hierarchy ID.

You actually have 2 problems, getting a reasonable hierarchy and then pivoting the result set. This may be of some use[^].

Never underestimate the power of human stupidity
RAH

Niladri_Biswas27-Jun-09 16:05

27-Jun-09 16:05

Sorry for being late in answering your question.
I was completely bogged up with my project.

However, please try this

SELECT F.CATEGORY1,F.CATEGORY2,F.CATEGORY3,F.CATEGORY4 

FROM(

SELECT	C1.TOPCATEGORYID AS CATEGORY1,
		C1.CATEGORYID AS CATEGORY2,
		C2.CATEGORYID AS CATEGORY3,
		C3.CATEGORYID AS CATEGORY4

FROM  CATEGORY AS C1
LEFT OUTER JOIN  CATEGORY AS C2
ON  C2.TOPCATEGORYID = C1.CATEGORYID
LEFT OUTER JOIN  CATEGORY AS C3
ON C3.TOPCATEGORYID = C2.CATEGORYID
WHERE C1.TOPCATEGORYID IS NOT NULL) F 

WHERE F.CATEGORY1  = 1

N.B.~ The inner derived query is the right one. It is showing the right dependencies. Since I had to bring precisely to your format, so I applied the outer query.

However, it will work as per the input you gave and as per your desired output

Let me know in case of any concerns.
Smile | :)

Niladri Biswas

Re: Fetching dependent records from the same table [modified]

Neeraj Kr27-Jun-09 16:42

Neeraj Kr

27-Jun-09 16:42

Hi,

I did not run the query, but as a dry run, it looks to me absolutely perfect. On Monday, after reaching office, I will try it and will send you the response. Can you kindly send me ur email id.

Thanks

-----Have A Nice Day-----

Niladri_Biswas27-Jun-09 16:59

Develop own small database-system

27-Jun-09 16:59

Dear Neeraj,

I have tested that query and with varying inputs. It worked fine in all the situations.

The inner query is doing the necessary job. It is bringing out all the dependent records.

I wrote the outer query only to satisfy your requirement.

But anyways, I enjoyed to solve this kind of queries.

By the way, you can always mail me by clicking on the email link provided here in the message section
Smile | :)

Niladri Biswas

modified on Saturday, June 27, 2009 11:16 PM

softwarejaeger26-Jun-09 6:21

26-Jun-09 6:21

Hello,

i'm searching a few weeks for a database which fits to my requirements and where i don't have to pay any cent. MySQL is very great, but i don't know what's with the licenses, do i need to pay for the db-system or only for the connector or not and so on...

So i wanted to ask, is it a good idea to create a "own" database? Maybe, for each Table an own, maybe XML-File or CSV-File? I don't need a lot of relations and so on, it must be only fast. And it mustn't be very secure, because only a server is talking to my DB over TCP. The hole thing user-management, or index-keys and so on, that's coded very fast, and that's not the problem. The problem is, how to save that files? I mean, how does a database load their data? I don't access for each SELECT-Command the HDD.

So how to make it, i have a lot of ideas, but i don't know if it's makeable. Or maybe could somebody explain how a DB-System like MySQL works? They have all their DB-Table-Files soemwhere on the filesystem.

Many thanks.

Luc Pattyn26-Jun-09 6:55

Luc Pattyn

26-Jun-09 6:55

Hi,

two suggestions:

1. for single-user applications, you could use Access; from a .NET perspective all this takes is an MDB file; once created (requiring Access as part of Office), you no longer need MS Access itself. Drawback is it has some functional limitations, and offers an SQL subset.

2. SQL Server Express is downloadable and free; it supports a limited number of users (10?) and a limited DB size (4GB?)

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles]

DISCLAIMER: this message may have been modified by others; it may no longer reflect what I intended, and may contain bad advice; use at your own risk and with extreme care.

softwarejaeger26-Jun-09 7:04

26-Jun-09 7:04

Well that's the problem, i don't know how many users will use my database Smile | :)

It could be only 10 or maybe 1000 till 5000 Smile | :)

And i have a lot data to save Smile | :)

Not big but many, it should be used on a webservice or a web-application.

Luc Pattyn26-Jun-09 7:08

Luc Pattyn

26-Jun-09 7:08

Have a look then at what your web host is offering.

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles]

DISCLAIMER: this message may have been modified by others; it may no longer reflect what I intended, and may contain bad advice; use at your own risk and with extreme care.

softwarejaeger26-Jun-09 7:14

26-Jun-09 7:14

It's my own server/s, or dedicated root servers.

So i "easily" need to storage data (a lot of data) and must access them pretty fast. Is there a way to do it on my own? What's with DataSet and XMLDataDocument? How is there the performance?

Luc Pattyn26-Jun-09 7:18

Luc Pattyn

26-Jun-09 7:18

I can't tell you from my own experience, I only use a small MySQL db.
Most people here seem to favor SQL Server (the non-Express one for real applications).

This is something I copied here from earlier this year:

16-JAN-2009 Mika Wendelius, on choosing a database
- for single user application with small amount of local data => SQL Server Compact Edition
- for multi user application with medium amount of shared data => SQL Server Express / Oracle XE
- for multi user application with large amount of shared data => SQL Server Std or Enterprise / Oracle 11g

I believe that the next question you would have is: what is a small or large amount of data. Again a matter of opinion but perhaps some quantities (these are certainly not accurate but give some idea):
- small amount could be less than 1 GB, perhaps less than a hundred tables and no table contain over 10 000 rows
- large amount could be more than 4 GB of actual data, perhaps 500+ tables tables and and some tables contain over 10 000 000 rows. User amount is high, let's say over 1000 concurrent.

And maybe this could be useful:
Comparison of relational database management systems
http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems[^]

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles]

DISCLAIMER: this message may have been modified by others; it may no longer reflect what I intended, and may contain bad advice; use at your own risk and with extreme care.

softwarejaeger26-Jun-09 7:34

26-Jun-09 7:34

Hm OK, i think that my DB really wouldn't have more than 4GB this should really be enough.
My idea was, if i use a db-system, i had the idea to make run it on linux. So MS-SQL isn't the right DB for me.

Web Apps only have 1 user

David Mujica26-Jun-09 7:21

David Mujica

26-Jun-09 7:21

Don't waste your time trying to develop a data storage engine. There have been hundreds of man-years invested in developing database engines. MS-Access is really quite fast and the license for MS-SQL server is really not that bad, if this is going to be a professional web application.

You speak of "clients" right ? Then they are paying a fee, right? Don't cheap out. Consider it an investment.

See my other post about starting with MS-Access and migrating to a more robust DB when you need it.

David Mujica26-Jun-09 7:16

David Mujica

26-Jun-09 7:16

If it is a web application, then you could argue that there is only 1 user making an actual connection to the database; the web app itself! Remember that this is not a client server application where you need to create a user logon for everyone who wants to access the database.

Hmmm ... things to think about.

Consider this, it sounds like you really don't know how much data you are going to store or how frequently it will be accessed, so why don't you start with MS-Access as the database and make sure that you design you application in such a manner that it would be relatively easy to change database enginges without causing a massive re-write. It can be done. Make sure you have a good data abstraction layer designed and you could actually have your application capable of running on all kinds of database engines, MS-SQL, MySQL, Oracle. It all starts with the design.

Just a thought. Cool | :cool:

softwarejaeger26-Jun-09 7:27

26-Jun-09 7:27

Not bad, my application now, can work with all this connectors. I must only exchange them. But i for example don't know how it is with this MySQL DB licenses!

I have for example 2 Server, one is the DB-Server and one is the "processing Server" (the server where the server-side-application is running) which has a connection to the db-server (and there's the problem with only 1 user, maybe i want to make a server-farm so i have a lot of connections). The client is talking only to the processing server, so i don't give any libraries or something away. Maybe like the PHP<->MySQL relation, there i don't need to pay for it and could make commercial applications. But why does MySQL say, that if i use it commercial i need to pay for a enterprise-license.

Has somebody any idea with this problem? And, now which method is the best only to try to make a own db-system? Should i save it "Binary", something like this?:

Table-schema-file:
Table:Test
Field:ID,isKey="true",length="10",type="string"
Field:sample,isKey="false",length="5",type="string"

Data-File:
TestID1   Hello!
TestID2   Hallo!

So there in the Data-File i have my Data saved, each line is a new record. Byte 0-9 is my Field "ID" (because of length-1) and Byte 10-14 is the "sample"-field.

Or should i save it into XML and read/query and so on it with XQuey? Then is the problem, maybe i have a DB-File which has a size of e.g. 4GB. How to update my DB-Files? I couldn't save every 5 Minutes 4GB files, if i only changed for example 500KB? Is there any solution to do this? I found something like XmlDataDocument and DataSet which works exactly, if i would work with a DB. Has anybody experiences with this?

Enver Maroshi26-Jun-09 7:33

Enver Maroshi

26-Jun-09 7:33

You don't save 4GB, you save only part of that file. You don't load whole file and then do something with it, you open partition of file this aprtions lets say is field in database, which has more space than it's filled (done so it can only add inside data, thats why database files are so large) and you fill that space with data. You use streams, you don't open file. And, it is really not that easy to make database engine. There is not only read-write, it's problem also in locking, so one does not owerwrite other user data, etc... auditing, transactions, etc...

With your knowladge, there is no way you could make database engine. Not at least you spend really long time on it. And let's say that 10 year licence for sql server would cost less then your working hours developing real database engine for 1 year Smile | :)

But if you insist on xml, etc... why not serialize objects to xml? Maybe a row by row as different file for faster loading, but many files wwould kill windows FS. Maybe create virtual FS. Oh, so much more to considere here, it is really not that easy to make your own database engine.

softwarejaeger26-Jun-09 7:49

26-Jun-09 7:49

Yes, that's right, but i have only very simple and "foolish" data. Ok, something like logging and so on, that's right, this could be a problem, but i think, before i overwrite something, i could read it first to check if it's the correct line.

Yes, but i heard, that the XML-Architecture in .NET shouldn't be very fast. I saw a lot of tutorials for using XmlDataDocument in DataSet. I have a simple xst-file where is my schema and my xml-file for the data. With those both files i'm able to work with my data inside of DataSets.

But what's better? My "own" System, save all records byte for byte in a file with streams or use the XmlDataDocument and DataSet. My Application works now with SQL-Databases, so i want to try it with this system, and if i see, that really nothing works, i could buy a mysql or mssql license. Another question, what's with MSDNAA licenses? I get a MSDNAA license of mysql, can i use that? And is it able to use that, if i want the client to pay for their client-application? (Not for the server-app or the db, only for their user-account).

softwarejaeger26-Jun-09 8:21