SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›297 »

uh zip zoom: May 28, 2003; Sensitive Thugs Need Hugs

I noticed that there was no general mega-thread for SQL related questions, and I happen to have a small one, so I figure I may as well create a general thread for stupid/small questions having to deal with any flavor of SQL. Here's mine:

The boss says that there is a bug in the where clause of the correlated subquery at the end having to do with the date, but I'm not seeing it.

code:

select pmg.pin as pin, pmg.tlid as primary_tlid, tt1.TIN as primary_tin, 
	pmgs.tlid as secondary_tlid, tt2.TIN as secondary_tin
from pin_mi_go pmg
inner join tblLocationTIN tlt1 on pmg.tlid = tlt1.tlid
inner join tblTIN tt1 on tlt1.TID = tt1.TID
inner join pin_mi_go_secondary pmgs on pmg.pin = pmgs.pin
inner join tblLocationTIN tlt2 on pmgs.tlid = tlt2.tlid
inner join tblTIN tt2 on tlt2.TID = tt2.TID
where tt1.TIN <> tt2.TIN
and exists (select TLID 
	from tblProvLicSpecLoc tplsl 
	where (tplsl.tlid = pmg.tlid 
	or tplsl.tlid = pmgs.tlid) 
	and (tplsl.DelDate < GETDATE()
	or tplsl.DelDate is null))

Please help.

:siren:

ATTENTION: PLEASE USE THE INSTANT SQL FORMATTER :siren:

this handy tool will format your SQL code into something remotely readable. Just look what it did to the above chunk of garbage:

code:

SELECT pmg.pin   AS pin,
       pmg.tlid  AS primary_tlid,
       tt1.tin   AS primary_tin,
       pmgs.tlid AS secondary_tlid,
       tt2.tin   AS secondary_tin
FROM   pin_mi_go pmg
       INNER JOIN tbllocationtin tlt1
         ON pmg.tlid = tlt1.tlid
       INNER JOIN tbltin tt1
         ON tlt1.tid = tt1.tid
       INNER JOIN pin_mi_go_secondary pmgs
         ON pmg.pin = pmgs.pin
       INNER JOIN tbllocationtin tlt2
         ON pmgs.tlid = tlt2.tlid
       INNER JOIN tbltin tt2
         ON tlt2.tid = tt2.tid
WHERE  tt1.tin <> tt2.tin
       AND EXISTS (SELECT tlid
                   FROM   tblprovlicspecloc tplsl
                   WHERE  (tplsl.tlid = pmg.tlid
                            OR tplsl.tlid = pmgs.tlid)
                          AND (tplsl.deldate < Getdate()
                                OR tplsl.deldate IS NULL))

uh zip zoom fucked around with this message at 01:01 on Nov 2, 2007

# ? Nov 1, 2007 19:52

Adbot: ADBOT LOVES YOU

# ? May 30, 2023 06:43

uh zip zoom: May 28, 2003; Sensitive Thugs Need Hugs

uh zip zoom posted:

I noticed that there was no general mega-thread for SQL related questions, and I happen to have a small one, so I figure I may as well create a general thread for stupid/small questions having to deal with any flavor of SQL. Here's mine:

The boss says that there is a bug in the where clause of the correlated subquery at the end having to do with the date, but I'm not seeing it.

Okay, apparently the problem was not so much my query but my lack of understanding of what the databases used for and how and why data is entered into it, so basically I needed to find dates that were greater than today, not dates that were smaller than, so it should have been:

code:

select pmg.pin as pin, pmg.tlid as primary_tlid, tt1.TIN as primary_tin, 
	pmgs.tlid as secondary_tlid, tt2.TIN as secondary_tin
from pin_mi_go pmg
inner join tblLocationTIN tlt1 on pmg.tlid = tlt1.tlid
inner join tblTIN tt1 on tlt1.TID = tt1.TID
inner join pin_mi_go_secondary pmgs on pmg.pin = pmgs.pin
inner join tblLocationTIN tlt2 on pmgs.tlid = tlt2.tlid
inner join tblTIN tt2 on tlt2.TID = tt2.TID
where tt1.TIN <> tt2.TIN
and exists (select TLID 
	from tblProvLicSpecLoc tplsl 
	where (tplsl.tlid = pmg.tlid 
	or tplsl.tlid = pmgs.tlid) 
	and (tplsl.DelDate > GETDATE()
	or tplsl.DelDate is null))

but still, people should throw their SQL questions that don't deserve their own thread in here.

# ? Nov 1, 2007 20:36

BlueJam: Mar 30, 2004

It's worth thinking about what format your date is in, it might be worth trying to use the cast() function to make both sides of the date compare the same.

# ? Nov 1, 2007 20:37

SLOSifl: Aug 10, 2002

Are you looking for data that occurred before TODAY or NOW?

edit: Oh, you had < when you needed >? Sounds like your boss could work as a bug tester at my office. "There's a bug :colbert:

# ? Nov 1, 2007 20:37

uh zip zoom: May 28, 2003; Sensitive Thugs Need Hugs

ashgromnies posted:

It's kind of hard to identify the issue with such limited information.

What is the bug, exactly? Like... what goes wrong? The query doesn't work at all, or it returns unintended results? How are the results unintended?

Those are some uuugly column and table names by the way

the query returned unintended results, because I misunderstood the purpose of that column.

I considered using cast to make getdate() a smalldatetime like the other side of the compare, but that really didn't have any effect.

Yes, the naming conventions are very confusing, and it's kind of a mess that I've inherited, but I've only been there a month and the position is entry-level. Sorting all of that out is one of my long long long term goals.

# ? Nov 1, 2007 21:07

ashgromnies: Jun 19, 2004

uh zip zoom posted:

Yes, the naming conventions are very confusing, and it's kind of a mess that I've inherited, but I've only been there a month and the position is entry-level. Sorting all of that out is one of my long long long term goals.

Yeah, sometimes your column names are all caps, other times camel case, other times... just random. That's rough.

# ? Nov 1, 2007 21:31

m5: Oct 1, 2001

Personally I think it's risky to perform relational comparisons on fields that might be null. That's what COALESCE and ISNULL (MSSQL) and CASE are for.

SQL does not short-circuit logical expressions, or, at least, MSSQL doesn't.

Also, for your SQL reformatting needs: the online SQL formatter.

# ? Nov 2, 2007 00:21

uh zip zoom: May 28, 2003; Sensitive Thugs Need Hugs

m5 posted:

Personally I think it's risky to perform relational comparisons on fields that might be null. That's what COALESCE and ISNULL (MSSQL) and CASE are for.

SQL does not short-circuit logical expressions, or, at least, MSSQL doesn't.

Also, for your SQL reformatting needs: the online SQL formatter.

thanks for the heads up on that link. I'll bookmark it.

Hopefully other people get from the thread title that this is intended to be a general small questions thread for SQL.

# ? Nov 2, 2007 00:56

Victor: Jun 18, 2004

m5 posted:

Personally I think it's risky to perform relational comparisons on fields that might be null. That's what COALESCE and ISNULL (MSSQL) and CASE are for.

Ehh, with three-valued logic, unknown or true = true, unknown and false = false, which is sufficient for the code posted in the date is the only nullable criteria-column. That being said, null will bite you in the rear end if you aren't careful (especially with subqueries that return a null). I want intellisense that tells me what columns are nullable, when I'm comparing different data types/lengths, and which columns are covered by indexes. Sigh, one can dream, right? Who knows how much intellisense will make it into SQL2008.

# ? Nov 2, 2007 04:33

Alex007: Jul 8, 2004

Victor posted:

Who knows how much intellisense will make it into SQL2008.

It doesn't display the nullable status of fields, but SQL Prompt does a great job at doing what intellisense would do:

http://www.red-gate.com/products/SQL_Prompt/index.htm

# ? Nov 2, 2007 14:51

Victor: Jun 18, 2004

Last time I tried SQL Prompt, I found that it requires you to type ctrl-space or something to activate the dropdown (or the dropdown would appear way too often), wouldn't autocomplete after pressing space, and other such annoyances. Maybe I've just acclimated to VS2005's intellisense, but I really like it, more than any other intellisense I've used. Oh, I don't think SQL Prompt does autocompletion for INFORMATION_SCHEMA, which I use a lot.

# ? Nov 2, 2007 15:48

Oben: Aug 7, 2004; Oh, the lights changed

Well, gently caress. Is there a way to use views in MySQL 4, or do I really have to go in and somehow rewrite the database and php code that accesses it?

# ? Nov 2, 2007 16:03

-JS-: Jun 1, 2004

Views are only available as of MySQL 5. If you can't upgrade, you have my condolences.

# ? Nov 2, 2007 18:01

SLOSifl: Aug 10, 2002

Victor posted:

Last time I tried SQL Prompt, I found that it requires you to type ctrl-space or something to activate the dropdown (or the dropdown would appear way too often), wouldn't autocomplete after pressing space, and other such annoyances. Maybe I've just acclimated to VS2005's intellisense, but I really like it, more than any other intellisense I've used. Oh, I don't think SQL Prompt does autocompletion for INFORMATION_SCHEMA, which I use a lot.

I had a problem with SQL Prompt because it would just sort of disappear every once in a while. It would work fine for a while, and then it wouldn't show up anymore.

Regarding nulls, as mentioned by Victor, they always evaluate to unknown. "null is null" is true, of course, but "null = null" is unknown. Anything plus null is null as well. This can be confusion if you don't know the behavior. The main problem is expecting "null = null" to work and using it in join or where clause.

code:

SELECT FirstName + ' ' + LastName as [Name] FROM Customers

If either of those are null, the whole statement is null. This is why you have to use COALESCE or some flow control to handle potentially null columns.

# ? Nov 2, 2007 19:21

shrike82: Jun 11, 2005

I have a table of book titles of which I only want titles that start with the letters A-K. Also, if the titles start with "A" or "An" or "The", ignore the first word and check on the 2nd word instead.

My first thought was to do a
WHERE Title REGEXP "(A |An |The )?[A-K]"
but it doesn't seem to be greedy so
"An T..." would be accepted. Is there a clean way to do this?

Also, does MySQL support REGEXP groupings? I can't seem to find a way to do so. I want to do a ORDER BY (Title REGEXP blah) where the first word is dropped if it is "A", "An" or "The".

Or is there a cleaner way to do all this in MySQL without touching REGEXPs?

# ? Nov 3, 2007 04:43

npe: Oct 15, 2004

leecming posted:

Also, does MySQL support REGEXP groupings? I can't seem to find a way to do so. I want to do a ORDER BY (Title REGEXP blah) where the first word is dropped if it is "A", "An" or "The".

If you can get your regex working, you should be able to fake this with an inline view: take the query you have above, add a column to your SELECT that uses functions to strip out the leading word, and then wrap the whole thing in another SELECT * FROM ( ...nasty regex query here... ) GROUP BY fixed_title.

I should point out that all of this is horribly expensive to the database, it is going to be doing massive table scans to get what you want. I hope there aren't a lot of records (or a lot of requests).

# ? Nov 3, 2007 19:40

Victor: Jun 18, 2004

Why not just do the following? Hopefully you have some character that is never in the title.

code:

replace(replace(replace(replace('^' + title, 
    '^A', ''), 
    '^An', ''), 
    '^The', ''), 
    '^', '')

If you don't want to rely on some character never being in the title, then you could just use a case when...end statement.

Edit: you're kinda screwed with Regex unless you want to use it to determine whether the first word is one of the above, and if so, find the index of the first space and grab the string after that space. (MySQL regexes only do matching, not capturing of groups or replacement.)

# ? Nov 3, 2007 20:47

Tinyn: Jan 10, 2003

I have a situation where I need to request a some data by id numbers, but that I need to get the data for some N number of id numbers. I could just call the simple stored proc N times, for each id number, but that performance sucks. Making a stored proc that takes 100 arguments, some of which might be NULL, to allow for however many id numbers I need at a time is obviously retarded.

Dynamic SQL is technically an option, but the way the DBAs think, it really isn't. How can I create a stored proc that will handle this problem?

# ? Nov 4, 2007 17:15

Victor: Jun 18, 2004

Find one of the many table-valued udfs that split strings, and then pass the ids as a delimited list.

# ? Nov 4, 2007 22:12

Xae: Jan 19, 2005

Tinyn posted:

I have a situation where I need to request a some data by id numbers, but that I need to get the data for some N number of id numbers. I could just call the simple stored proc N times, for each id number, but that performance sucks. Making a stored proc that takes 100 arguments, some of which might be NULL, to allow for however many id numbers I need at a time is obviously retarded.

Dynamic SQL is technically an option, but the way the DBAs think, it really isn't. How can I create a stored proc that will handle this problem?

Who is your Database Vendor?

Options:
Pass an Array/Stack in.
Temporary Table
Batch Table: 2 Row Table, a Batch_ID, and a Key for the other ID numbers, pass the Batch_ID in to the function.

# ? Nov 5, 2007 00:23

Dakha: Feb 18, 2002; Fun Shoe

Here's a pretty general question, I'm extracting data from a system, and there's only a few places where we have a known key to use, and often it's a really, really big number of keys. Along the lines of:

SELECT *
FROM materials
WHERE matnr IN ( set of 50-100000 material numbers).

These sorts of queries often fail to execute, as the input parameter files are just too large. When we split them up and run the union of queries, it takes much, much longer.

Is there some simple way we can rework things so that the extraction can be done in a reasonable amount of time? Some of the more complicated queries are taking FAR too long to run

# ? Nov 5, 2007 16:12

Ardhanari: Mar 23, 2006

Dakha posted:

Is there some simple way we can rework things so that the extraction can be done in a reasonable amount of time? Some of the more complicated queries are taking FAR too long to run

Stuff the IDs into a temp table and select a join off it?

# ? Nov 5, 2007 16:36

npe: Oct 15, 2004

Dakha posted:

Is there some simple way we can rework things so that the extraction can be done in a reasonable amount of time? Some of the more complicated queries are taking FAR too long to run

We have a similar situation to this, and we've been experimenting on the best option for performance. We're an Oracle shop though, so I don't know how much this will help for your case, but we have to search for millions of records and then order the output very specifically. Selecting using IN lists was sucking. What we do now is bulk load the list of values to a temporary table, create an index, and then simply query with a join against this table.

Since we are loading to a temporary scratch table (that we create on the fly, immediately) we are able to use Oracle sqlldr with direct path loading, which is ridiculously fast (basically it's a bulk write straight onto disk). This means that the short amount of time we lose doing the table/index create is more than made up for by the time saved in being able to query out arbitrarily large sets of values.

# ? Nov 5, 2007 16:38

Victor: Jun 18, 2004

Satan's Scallion posted:

What we do now is bulk load the list of values to a temporary table, create an index, and then simply query with a join against this table.

Maybe there is no analog on Oracle, but in SQL Server, one would just create a one-column table with the clustered index on that column; no additional index would be needed. (Clustered indexes are simply indexes that define how the rows are physically ordered.)

# ? Nov 5, 2007 16:54

duck monster: Dec 15, 2004

Oben posted:

Well, gently caress. Is there a way to use views in MySQL 4, or do I really have to go in and somehow rewrite the database and php code that accesses it?

Views have changed my life. For serious. I used to *hate* writing SQL stuff in PHP, and in fact I still do.

Being an Object Oriented nut, I'm deeply fond of things like Active Record and its ilk. The problem with Active Record is that it tends to suck for complex queries.

So along comes Views and its all better now. You just use a view to turn a complex query into a simple minded query, and then your code magically starts being lovely and simple. The beauty of it is, since data tends to repeatedly be looked at the same way over and over, you just keep reuseing the same view.

Caveat: Beware of how agregation and parameterised querys nest when doing things like views of views.

code:

DROP VIEW IF EXISTS `<yourviewname>`;

CREATE
    VIEW `<yourviewname>` 
	AS 
            (
                 <stick some SQL in here>
             )

Copy and paste that poo poo, for instant View love. Its that easy. Also, views in theory are much faster than constantly feeding big querys over and over again into it. Pre-compilation or something. Not entirely sure why, but when we benchmarked it at work, the speed differences where outrageous at times.

Caveat. Views tend to dislike this sort of thing;- SELECT * FROM TABLE
Your better off using something like SELECT raraID,raraName,raraHeyMan FROM TABLE . This means you need to watch what happens with your views when you change the tables they are dependant on. If you add a field to a dependancy , you might need to add that field to the view. I tend to keep a views.txt file with my code project s that have copies of all the views inside them.

duck monster fucked around with this message at 22:30 on Nov 5, 2007

# ? Nov 5, 2007 17:32

Xae: Jan 19, 2005

duck monster posted:

Copy and paste that poo poo, for instant View love. Its that easy. Also, views in theory are much faster than constantly feeding big querys over and over again into it. Pre-compilation or something. Not entirely sure why, but when we benchmarked it at work, the speed differences where outrageous at times.

Caveat. Views tend to dislike this sort of thing;- SELECT * FROM TABLE
Your better off using something like SELECT raraID,raraName,raraHeyMan FROM TABLE . This means you need to watch what happens with your views when you change the tables they are dependant on. If you add a field to a dependancy , you might need to add that field to the view. I tend to keep a views.txt file with my code project s that have copies of all the views inside them.

One of the reasons Views can sometimes perform better than ad-hoc query throwing is that the Optimizer/Planner will have a set plan for it. If you send things in at will the DB engine may have to hard-parse. This is where the the database will optimize the query and generate a new plan. Often times for small, simple joins the act of figuring out what the query wants takes longer than retrieving the data. If you are careful about how you write your SQL statements and you always right them in nearly the same way you can get a soft-parse which won't invoke so much overhead. This is the mechanic that the view works with to improve performance. Since the SQL is executed the exact same way each time, you get soft-parses instead of hard-parses.

# ? Nov 5, 2007 18:53

npe: Oct 15, 2004

Victor posted:

Maybe there is no analog on Oracle, but in SQL Server, one would just create a one-column table with the clustered index on that column; no additional index would be needed. (Clustered indexes are simply indexes that define how the rows are physically ordered.)

Well, I'm simplifying things a bit here. We generally have several columns, with multiple indexes due to the nature of our work. My only point was that even with the cost of building the indexes, we still came out ahead in terms of overall execution time with this approach.

# ? Nov 5, 2007 19:31

Dakha: Feb 18, 2002; Fun Shoe

Cheers guys. I made a table, and it makes all the difference :cool:

# ? Nov 5, 2007 20:30

chocojosh: Jun 9, 2007; D00D.

So essentially I want to do:
1) EXEC 'Some 40 line select statement that dynamically picks the table'
2) Insert the result of (1) into a temp table
3) sp_executesql 'Parameterized search query into the table created in (2)'.

Suggestions for step 2? Any links on how to make temporary tables in SQL Server 2005, and anything I should know?

(More details, but not important)

I've started a stored proc that starts with a fairly complicated select statement to retrieve a bunch of information. The problem is that I'm using the same stored proc for different tables and I use EXEC with the table name at the top (Yes, I know this is horrible, but I don't want to have to write a new stored proc for each table).

After I get all this data, I need to filter it by a bunch of search conditions, based on the user's input and passed as parameters to the stored proc. For this I want to use sp_executesql (because it prevents SQL injection, as I learned when I used exec and entered ' in my search condition). sp_executesql will not accept dynamic table names or field names to be entered.

# ? Nov 6, 2007 17:17

Xae: Jan 19, 2005

chocojosh posted:

So essentially I want to do:
1) EXEC 'Some 40 line select statement that dynamically picks the table'
2) Insert the result of (1) into a temp table
3) sp_executesql 'Parameterized search query into the table created in (2)'.

Suggestions for step 2? Any links on how to make temporary tables in SQL Server 2005, and anything I should know?

(More details, but not important)

I've started a stored proc that starts with a fairly complicated select statement to retrieve a bunch of information. The problem is that I'm using the same stored proc for different tables and I use EXEC with the table name at the top (Yes, I know this is horrible, but I don't want to have to write a new stored proc for each table).

After I get all this data, I need to filter it by a bunch of search conditions, based on the user's input and passed as parameters to the stored proc. For this I want to use sp_executesql (because it prevents SQL injection, as I learned when I used exec and entered ' in my search condition). sp_executesql will not accept dynamic table names or field names to be entered.

I don't know if MSSQL does, but Oracle supports inserting from a select.
Edit: It looks like MSSQL supports it, with similar syntax.
Link
You could do:

code:

INSERT INTO my.insert_table(ins_col_1, ins_col_2, ins_etc)
(SELECT	Col_1,
	Col_2,
	etc
FROM	my.table
WHERE	condition = 1
)

It is generally easier and more efficient to have the Database do filtering than doing it your self. Unless you are filtering by non-indexed rows on a very large table.

Temp Table Link

Xae fucked around with this message at 19:53 on Nov 6, 2007

# ? Nov 6, 2007 19:48

chocojosh: Jun 9, 2007; D00D.

Thanks for the link.

I'm wondering if there's any "gotchas" for using temp tables or table variables (and what's the difference between the two).

I'm not sure what you mean by

"It is generally easier and more efficient to have the Database do filtering than doing it your self. Unless you are filtering by non-indexed rows on a very large table."

# ? Nov 6, 2007 20:34

Xae: Jan 19, 2005

chocojosh posted:

Thanks for the link.

I'm wondering if there's any "gotchas" for using temp tables or table variables (and what's the difference between the two).

I'm not sure what you mean by

"It is generally easier and more efficient to have the Database do filtering than doing it your self. Unless you are filtering by non-indexed rows on a very large table."

This part of your first post:

quote:

After I get all this data, I need to filter it by a bunch of search conditions,

Made it sound like you wanted to return a large query then filter it your self.

The only Gotcha I'm aware of with Temp Tables is a bug in Oracle if you execute too many transactions against it, but I haven't used the much in MSSQL.

# ? Nov 6, 2007 20:37

Victor: Jun 18, 2004

chocojosh posted:

sp_executesql will not accept dynamic table names or field names to be entered.

Correct; all it allows is for parametrization and outputting scalar results to variables in the calling code.

chocojosh posted:

I'm wondering if there's any "gotchas" for using temp tables or table variables (and what's the difference between the two).

Indexes cannot be created on table variables. You cannot access table variables in sp_executesql statements that were declared in the calling code. Table variables are local to the calling code, while temporary tables are local to the current connection (#) or all connections (##).

I want to know why Microsoft hasn't worked on passing tables around, or at least views that can be dynamically selected from without dynamic sql.

# ? Nov 6, 2007 20:49

chocojosh: Jun 9, 2007; D00D.

Victor posted:

Correct; all it allows is for parametrization and outputting scalar results to variables in the calling code.

Indexes cannot be created on table variables. You cannot access table variables in sp_executesql statements that were declared in the calling code. Table variables are local to the calling code, while temporary tables are local to the current connection (#) or all connections (##).

I want to know why Microsoft hasn't worked on passing tables around, or at least views that can be dynamically selected from without dynamic sql.

Alright, thank you! I'm going to use a table variable because I don't want it to be accessed out of the calling code or to anyone else in the connection. I found this Good overview of temp tables and table variables that also makes this choice clearer: http://www.odetocode.com/Articles/365.aspx

I think that support for selecting dynamically from views or tables would be great, and would be a huge help for me. The topic is discussed a bit here: http://www.sommarskog.se/dynamic_sql.html#Dyn_table -- I've been wondering HOW to implement his pre-processor idea.

Xae: Essentially I need to first get all my data, and then filter it by doing a second select statement with dynamic where conditions (i.e. I build up a where clause for each field the user can possibly search by). By filtering I meant a second select statement that can remove rows from the temp table; not some kind of filtering in my programming language

I honestly wish there was an easier solution, but not if I want to use sp_executesql which I need to use (this makes the choco very sad).

# ? Nov 6, 2007 21:49

Xae: Jan 19, 2005

chocojosh posted:

Xae: Essentially I need to first get all my data, and then filter it by doing a second select statement with dynamic where conditions (i.e. I build up a where clause for each field the user can possibly search by). By filtering I meant a second select statement that can remove rows from the temp table; not some kind of filtering in my programming language I honestly wish there was an easier solution, but not if I want to use sp_executesql which I need to use (this makes the choco very sad).

Is there a reason you can't to it all in one statement? Is this a two step process, where a user selects one group then refines? Whenever possible I try to avoid Selecting the same data twice.

# ? Nov 6, 2007 21:56

chocojosh: Jun 9, 2007; D00D.

Xae posted:

Is there a reason you can't to it all in one statement? Is this a two step process, where a user selects one group then refines? Whenever possible I try to avoid Selecting the same data twice.

This is due to the limitation of sp_executesql.

I am choosing my table dynamically, based on an input parameter (I *know* you shouldn't do this due to lack of query caching). To do this requires using the exec statement which can make you a victim to sql injection.

sp_executesql protects you against sql injection; but, it does not allow you to choose your table name dynamically (it will only allow you to pass in parameters for your where clause).

I use exec to do the dynamic table lookup thingie, and then do an sp_executesql to be able to do a dynamic search (I use dynamic searching conditions because the search conditions vary based on the number of characters inputted, and doing a different statement for each combination would force me to have a lot of code duplication).

# ? Nov 7, 2007 03:20

Red_Chief: Nov 2, 2007

Alex007 posted:

It doesn't display the nullable status of fields, but SQL Prompt does a great job at doing what intellisense would do:

http://www.red-gate.com/products/SQL_Prompt/index.htm

I tried SQL Prompt before but it seemed laggy (this was before Red-Gate bought them). Has it improved?

Shameless plug - RedGates SQL Compare is one of the best sql db tools out there.

# ? Nov 7, 2007 05:21

Victor: Jun 18, 2004

chocojosh posted:

I am choosing my table dynamically, based on an input parameter (I *know* you shouldn't do this due to lack of query caching). To do this requires using the exec statement which can make you a victim to sql injection.

sp_executesql protects you against sql injection; but, it does not allow you to choose your table name dynamically (it will only allow you to pass in parameters for your where clause).

As long as you're smart and do replace(varchar_variable, '''', ''''''), you're safe from SQL injection. This isn't to say that parametrization is bad, just that it isn't a must.

You can do dynamic SQL with sp_executesql just like you can with exec(sql). sp_executesql only allows scalar parameters, which may be what you were getting at.

# ? Nov 7, 2007 06:00

pig-fukr: Nov 3, 2002

Victor posted:

I want to know why Microsoft hasn't worked on passing tables around, or at least views that can be dynamically selected from without dynamic sql.

They have. In 2008 you get table-valued parameters that can be passed around, provided you're willing to strongly type them and read-only access will do what you need:

http://blogs.techrepublic.com.com/datacenter/?p=168

# ? Nov 7, 2007 15:57

Adbot: ADBOT LOVES YOU

# ? May 30, 2023 06:43

chocojosh: Jun 9, 2007; D00D.

Victor posted:

As long as you're smart and do replace(varchar_variable, '''', ''''''), you're safe from SQL injection. This isn't to say that parametrization is bad, just that it isn't a must.

You can do dynamic SQL with sp_executesql just like you can with exec(sql). sp_executesql only allows scalar parameters, which may be what you were getting at.

replace(varchar_variable, '''', '''''')

Is that the *ONLY* thing I need to do to avoid SQL injection? Can you provide me with a link (not that I don't trust you, but I'd rather not leave security holes in my software due to my sloppyness)? If so, I may just use one long exec string instead of the table variable.

# ? Nov 7, 2007 16:03

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›297 »