[MSSQL] Kolommen samenvoegen in query tot 1

[Urk]

Ik sla in een MS SQL 2000 database de voornaam, tussenvoegsel en achternaam apart op van een gebruiker.
Later wil ik in de naam kunnen zoeken met LIKE '%zoekennaar%'

Echter wil ik bijv ook op de hele naam kunnen zoeken, dus als iemand Piet van Straten heet moet ik hierop kunnen zoeken en moet dit resultaat geven als "Piet" in de voornaam kolom staat, "van" in de tussenvoegsel kolom en "Straten" in de achternaam kolom.

Heb al wat geprobeerd met MS SQL:

SQL:

SELECT voornaam + tussenvoegsel + achternaam AS volledigenaam
FROM gebruikers

en

SQL:

SELECT voornaam + ' ' + tussenvoegsel + ' ' + achternaam AS volledigenaam
FROM gebruikers

Dit gaat opzich aardig goed.
De voornaam en achternaam velden zijn verplicht, en zijn dus altijd gevuld, echter het tussenvoegsel veld is optioneel.
Als ik nu een <NULL> value als tussenvoegsel in een record heb staan gaat de query niet goed.
En maakt hij van het resultaat volledigenaam ook <NULL>

Hoe kan ik dit oplossen?
Alvast

[gorgi_19]

IsNull

[Urk]

gorgi_19 schreef op vrijdag 25 februari 2005 @ 01:43:
IsNull

En hoe bedoel je dat dan precies? Moet ik kan in m'n ASP code regelen of in SQL?
Zoals bijv:

SQL:

SELECT voornaam + ' ' + ISNULL(tussenvoegsel) + ' ' + achternaam AS volledigenaam
FROM gebruikers

_{Echter werkt dit niet}

[Urk]

Dit vind ik ook nog in SQL Books online:

When you concatenate null values, either the concat null yields null setting of sp_dboption or SET CONCAT_NULL_YIELDS_NULL determines the behavior when one expression is NULL. With either concat null yields null or SET CONCAT_NULL_YIELDS_NULL enabled ON, 'string' + NULL returns NULL. If either concat null yields null or SET CONCAT_NULL_YIELDS_NULL is disabled, the result is 'string'.

Heeft dit met hetzelfde te maken of iets anders? Ik snap namelijk niks van bovenstaande tekst.

[Sayko]

Where tussenvoegsel is not null?

[Urk]

Ah, ik ben een stapje dichterbij, dit werkt bijna:

SQL:

SET CONCAT_NULL_YIELDS_NULL OFF
SELECT voornaam + ' ' + tussenvoegsel + ' ' + achternaam AS volledigenaam
FROM gebruikers

Dan nog 1 probleem, als het tussenvoegsel <NULL> is laat hij 2 spaties tussen de voornaam en achternaam staan, dit moet er natuurlijk 1 zijn, maar hoe krijg ik dit nog weg?

[ Voor 13% gewijzigd door Urk op 25-02-2005 01:55 ]

[Urk]

Hoodlum2000 schreef op vrijdag 25 februari 2005 @ 01:51:
Where tussenvoegsel is not null?

Nee, dat is niet handig, want als er geen tussenvoegsel is moet hij nog steeds de voor- & achternaam samenvoegen

[gorgi_19]

ISNULL
Replaces NULL with the specified replacement value.

Syntax
ISNULL ( check_expression , replacement_value )

Arguments
check_expression

Is the expression to be checked for NULL. check_expression can be of any type.

replacement_value

Is the expression to be returned if check_expression is NULL. replacement_value must have the same type as check_expresssion.

Return Types
Returns the same type as check_expression.

Remarks
The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned.

Examples
A. Use ISNULL with AVG
This example finds the average of the prices of all titles, substituting the value $10.00 for all NULL entries in the price column of the titles table.

USE pubs
GO
SELECT AVG(ISNULL(price, $10.00))
FROM titles
GO

Here is the result set:

--------------------------
14.24

(1 row(s) affected)

B. Use ISNULL
This example selects the title, type, and price for all books in the titles table. If the price for a given title is NULL, the price shown in the result set is 0.00.

USE pubs
GO
SELECT SUBSTRING(title, 1, 15) AS Title, type AS Type,
ISNULL(price, 0.00) AS Price
FROM titles
GO

Here is the result set:

Title Type Price
--------------- ------------ --------------------------
The Busy Execut business 19.99
Cooking with Co business 11.95
You Can Combat business 2.99
Straight Talk A business 19.99
Silicon Valley mod_cook 19.99
The Gourmet Mic mod_cook 2.99
The Psychology UNDECIDED 0.00
But Is It User popular_comp 22.95
Secrets of Sili popular_comp 20.00
Net Etiquette popular_comp 0.00
Computer Phobic psychology 21.59
Is Anger the En psychology 10.95
Life Without Fe psychology 7.00
Prolonged Data psychology 19.99
Emotional Secur psychology 7.99
Onions, Leeks, trad_cook 20.95
Fifty Years in trad_cook 11.95
Sushi, Anyone? trad_cook 14.99

(18 row(s) affected)

[nescafe]

Een NULL-waarde samen met een string is ook NULL.

edit:
mits je CONCAT_NULL_YIELDS_NULL (standaard) aan hebt gezet natuurlijk

Gebruik daarom COALESCE om deze waarde in zijn geheel wel of niet op te nemen:
SELECT voornaam + ' ' + COALESCE(tussenvoegsel + ' ', '') + achternaam AS volledigenaam

[ Voor 24% gewijzigd door nescafe op 25-02-2005 02:09 ]

[Urk]

gorgi_19 schreef op vrijdag 25 februari 2005 @ 02:03:
[...]

Grappig, dat was ik net aan het lezen, maar ik moet volgens mij juist IS NOT NULL hebben, die hij dan vervangt met een spatie. Met ISNULL() lukt het volgens mij ook niet...

[Urk]

nescafe schreef op vrijdag 25 februari 2005 @ 02:04:
Een NULL-waarde samen met een string is ook NULL.

edit:
mits je CONCAT_NULL_YIELDS_NULL (standaard) aan hebt gezet natuurlijk

Gebruik daarom COALESCE om deze waarde in zijn geheel wel of niet op te nemen:
SELECT voornaam + ' ' + COALESCE(tussenvoegsel + ' ', '') + achternaam AS volledigenaam

Geweldig! Las net je edit, want dacht al dat het niet werkte.
Na CONCAT_NULL_YIELDS_NULL op ON te hebben gezet gaat het helemaal goed!

Heb ook de SQL help over COALESCE gelezen maar snap toch nog niet in normaal nederlands wat deze functie nou precies doet...Kun je dat wellicht nog uitleggen?

Mijn dank is wederom weer

[nescafe]

COALESCE is een functie welke zijn eerste waarde teruggeeft welke niet-null is.

Kijk naar het voorbeeld:

SQL:

CASE
   WHEN (expression1 IS NOT NULL) THEN expression1
   ...
   WHEN (expressionN IS NOT NULL) THEN expressionN
   ELSE NULL

Stel tussenvoegsel = NULL:
COALESCE(tussenvoegsel + ' ', '') = COALESCE(NULL + ' ', '') = COALESCE(NULL, '') = ''

Stel tussenvoegsel = 'ten':
COALESCE(tussenvoegsel + ' ', '') = COALESCE('ten' + ' ', '') = COALESCE('ten ', '') = 'ten '

[Urk]

nescafe schreef op vrijdag 25 februari 2005 @ 02:17:
COALESCE is een functie welke zijn eerste waarde teruggeeft welke niet-null is.

Kijk naar het voorbeeld:

SQL:

CASE
   WHEN (expression1 IS NOT NULL) THEN expression1
   ...
   WHEN (expressionN IS NOT NULL) THEN expressionN
   ELSE NULL

Stel tussenvoegsel = NULL:
COALESCE(tussenvoegsel + ' ', '') = COALESCE(NULL + ' ', '') = COALESCE(NULL, '') = ''

Stel tussenvoegsel = 'ten':
COALESCE(tussenvoegsel + ' ', '') = COALESCE('ten' + ' ', '') = COALESCE('ten ', '') = 'ten '

OK, dank je! Ik begrijp het, zal de tekst in SQL ook nog eens doorlezen!

[Urk]

Hmmmm...nu werkt dit niet:

SQL:

SET CONCAT_NULL_YIELDS_NULL ON 
SELECT gebruiker_id, gebruiker_username,
gebruiker_voornaam + ' ' + COALESCE (gebruiker_tussenvoegsel + ' ', '') + gebruiker_achternaam AS gebruiker_volledigenaam,
gebruiker_telefoon,
gebruiker_gsm,
gebruiker_email,
gebruiker_actief,
groep_groepsnaam
FROM gebruikers INNER JOIN groepen ON gebruikers.gebruiker_groep = groepen.groep_id
WHERE gebruiker_volledigenaam LIKE '%zoekennaar%'
ORDER BY gebruiker_actief DESC, gebruiker_username

En krijg ik de foutmelding "Invalid column name 'gebruiker_volledigenaam'."
Kan dit niet ofzo? Je kan die AS kolomnaam toch wel weer gebruiken in je WHERE expressie?

[nescafe]

Je kunt AS-kolomaliassen alleen in je HAVING-clausule opnemen.
Wel kun je in je WHERE-clausule de volledige kolominhoud opnemen, zoals bijvoorbeeld:

code:

WHERE gebruiker_voornaam + ' ' + COALESCE (gebruiker_tussenvoegsel + ' ', '') + gebruiker_achternaam LIKE '%zoekennaar%'

Ik hoop dat het overbodig is om te zeggen, maar pas je in je code-implementatie op voor SQL insertion?

[Urk]

nescafe schreef op vrijdag 25 februari 2005 @ 02:29:
Je kunt AS-kolomaliassen alleen in je HAVING-clausule opnemen.
Wel kun je in je WHERE-clausule de volledige kolominhoud opnemen, zoals bijvoorbeeld:

code:

WHERE gebruiker_voornaam + ' ' + COALESCE (gebruiker_tussenvoegsel + ' ', '') + gebruiker_achternaam LIKE '%zoekennaar%'

Ik hoop dat het overbodig is om te zeggen, maar pas je in je code-implementatie op voor SQL insertion?

Aha, dit werkt inderdaad wel:

SQL:

SET CONCAT_NULL_YIELDS_NULL ON 
SELECT gebruiker_id, gebruiker_username,
gebruiker_voornaam + ' ' + COALESCE (gebruiker_tussenvoegsel + ' ', '') + gebruiker_achternaam AS gebruiker_volledigenaam,
gebruiker_telefoon,
gebruiker_gsm,
gebruiker_email,
gebruiker_actief,
groep_groepsnaam
FROM gebruikers INNER JOIN groepen ON gebruikers.gebruiker_groep = groepen.groep_id
WHERE gebruiker_voornaam + ' ' + COALESCE (gebruiker_tussenvoegsel + ' ', '') + gebruiker_achternaam LIKE '%zoekennaar%'
ORDER BY gebruiker_actief DESC, gebruiker_username

Dank je!
En wat bedoel je precies met:

Ik hoop dat het overbodig is om te zeggen, maar pas je in je code-implementatie op voor SQL insertion?

Dit is namelijk alleen een SELECT query, en geen INSERT.

[nescafe]

Urk schreef op vrijdag 25 februari 2005 @ 02:32:
[...]

En wat bedoel je precies met:

[...]

Dit is namelijk alleen een SELECT query, en geen INSERT.

Ik neem aan dat het gedeelte 'zoekennaar' wordt ingevuld met een term die de gebruiker ingeeft. Als je deze invoer niet controleert dan kan de gebruiker daar hele enge vieze woorden inzetten waardoor er allerlei enge dingen gebeuren met je database.

Zie ook:
P&W FAQ - SQL
P&W FAQ - Hoe beveilig ik een website?

offtopic:
Lijkt wel een slowchat

[Urk]

Oh zo bedoel je! Ja, ik heb nu een functie die ik gebruik en om alle data heenzet die de database ingaat en datainvoer die word uitgelezen:

ASP:

Function FixSQL(Variable)
 FixSQL = Replace(Variable,"'","''")
End Function

Deze is nog niet helemaal goed omdat hij alleen een single quote aanpakt.
Om het volledig veilig te doen moet ik toch de single quote ('), de double quote (") en de backslashes (\) vervangen/escapen?

[gorgi_19]

Nee, dan moet je met parametrized queries gaan werken.

[Urk]

Ik zal dit nog eens doorlezen: P&W FAQ - PHP
Weliswaar geschreven voor PHP maar neem aan dat dat niet uitmaakt of het ASP of PHP is, toch?

[P_de_B]

Kijk hier maar eens naar: P&W FAQ - SQL

[whoami]

Een mens kan moeilijk doen...

code:

select voornaam + ' ' + isnull( tussenvoegsel, '') + ' ' + straat

Voor je quotes etc... moet je idd parametrized queries gebruiken.

[curry684]

Urk schreef op vrijdag 25 februari 2005 @ 02:12:
[...]

Geweldig! Las net je edit, want dacht al dat het niet werkte.
Na CONCAT_NULL_YIELDS_NULL op ON te hebben gezet gaat het helemaal goed!

Heb ook de SQL help over COALESCE gelezen maar snap toch nog niet in normaal nederlands wat deze functie nou precies doet...Kun je dat wellicht nog uitleggen?

Mijn dank is wederom weer

Da's natuurlijk een boerenlullenoplossing: die optie is gewoon standaard en moet je met je fikken vanaf blijven

COALESCE is overkill, da's gewoon een teveel-parameters-ISNULL. Je zoekt gewoon dit:

SQL:

SELECT ISNULL(FirstName, ' ') + ' ' + 
       ISNULL(MiddleName, ' ') + ' ' + 
       ISNULL(RightName, ' ')
FROM Names WHERE Id = @id;

En dan nog even trimmen op de correcte plek

[ Voor 3% gewijzigd door curry684 op 25-02-2005 08:49 ]

[Anoniem: 14829]

Urk schreef op vrijdag 25 februari 2005 @ 02:44:
Om het volledig veilig te doen moet ik toch de single quote ('), de double quote (") en de backslashes (\) vervangen/escapen?

Dat is niet helemaal voldoende...
Bij MSSQL moet je ook nog waarden zien te voorkomen / weigeren die de volgende zaken bevatten:
'--' Begin van een comment, MSSQL negeert vervolgens alles wat erachter staat.
'master.' Toegang tot de systeemtabellen, nooit nodig voor een eindgebruiker.
'xp_' MSSQL's extended procedures. Erg krachtig, maar geven wel zo ongeveer toegang tot de hele server...

Leuk voorbeeldje uit deze link:
<---------
Example 27-5. Attacking the database hosts operating system (MSSQL Server)

PHP:

$query  = "SELECT * FROM products WHERE id LIKE '%$prod%'";
$result = mssql_query($query);

If attacker submits the value a%' exec master..xp_cmdshell 'net user test testpass /ADD' -- to $prod, then the $query will be:

PHP:

$query  = "SELECT * FROM products
                   WHERE id LIKE '%a%'
                   exec master..xp_cmdshell 'net user test testpass /ADD'--";
$result = mssql_query($query);

MSSQL Server executes the SQL statements in the batch including a command to add a new user to the local accounts database. If this application were running as sa and the MSSQLSERVER service is running with sufficient privileges, the attacker would now have an account with which to access this machine.
--------->
Je ziet, select-queries zijn ook niet echt veilig...

[whoami]

Afterlife: Tja, daar komen parametrized queries van pas....

[Anoniem: 14829]

Weet ik, en ik gebruik zelf ook uitsluitend geparametriseerde queries. (nou ja, soms smokkel ik een beetje, omdat bv. 'select * from :TabelNaam order by :VeldNaam' niet gaat werken...)
Niet alleen vanwege de veiligheid, maar wanneer je je queries parametriseert, geef je de database tenminste ook nog de kans om die query te optimaliseren. Zeker wanneer die query vaak wordt aangeroepen kan dat aardig in performance schelen.

Maar TS schreef: "Dit is namelijk alleen een SELECT query, en geen INSERT", vandaar mijn aanvulling.

[nescafe]

whoami schreef op vrijdag 25 februari 2005 @ 08:48:
Een mens kan moeilijk doen...

code:

select voornaam + ' ' + isnull( tussenvoegsel, '') + ' ' + straat

[...]

Dit ben ik niet met je eens whoami, ik vraag me zelfs af of je de draad wel gelezen hebt. (Ik neem trouwens aan dat je achternaam bedoelt?)

curry684 schreef op vrijdag 25 februari 2005 @ 08:48:
[...]

COALESCE is overkill, da's gewoon een teveel-parameters-ISNULL. Je zoekt gewoon dit:

SQL:

SELECT ISNULL(FirstName, ' ') + ' ' + 
       ISNULL(MiddleName, ' ') + ' ' + 
       ISNULL(RightName, ' ')
FROM Names WHERE Id = @id;

En dan nog even trimmen op de correcte plek

Stel MiddleName is als enige NULL dan zul je zelfs FirstName + ' ' + ' ' + ' ' + RightName krijgen. Hoe wil je die TRIM's (LTRIM & RTRIM neem ik aan?) toepassen en zou dit qua performance beter zijn dan een enkele (of dubbele) COALESCE? Stel, Voornaam en Achternaam kunnen ook NULL zijn, dan kom uit op het volgende stukje SQL:

SQL:

SELECT COALESCE(VoorNaam + ' ', '') + COALESCE(TussenVoegsel + ' ', '') + ISNULL(AchterNaam, '') VolledigeNaam

[Anoniem: 14829]

nescafe, in jouw COALESCE voorbeeld krijg je 2 spaties tussen VoorNaam en AchterNaam wanneer TussenVoegsel '' is i.p.v. NULL. Trimmen is dan een stuk praktischer.
Ik weet 't, 't is muggenziften, maar wanneer klanten daarover vallen, val ik er ook over.

[ Voor 6% gewijzigd door Anoniem: 14829 op 25-02-2005 14:30 ]

[nescafe]

Okee dat begrijp ik, maar hoe moet je die trim's dan toepassen?

[Urk]

Anoniem: 14829 schreef op vrijdag 25 februari 2005 @ 14:30:
nescafe, in jouw COALESCE voorbeeld krijg je 2 spaties tussen VoorNaam en AchterNaam wanneer TussenVoegsel '' is i.p.v. NULL. Trimmen is dan een stuk praktischer.
Ik weet 't, 't is muggenziften, maar wanneer klanten daarover vallen, val ik er ook over.

Nee, als je goed gelezen had verder naar boven had je gezien dat dat niet waar is, als je namelijk het volgende voor je query plaatst:

SQL:

SET CONCAT_NULL_YIELDS_NULL ON

En zoals ik zei is mijn tussenvoegsel of gevuld, dus minimaal 1 karakter of hij is <NULL>

[ Voor 10% gewijzigd door Urk op 25-02-2005 14:32 ]

[Anoniem: 14829]

nescafe schreef op vrijdag 25 februari 2005 @ 14:31:
Okee dat begrijp ik, maar hoe moet je die trim's dan toepassen?

't Is te doen, maar je wordt gek van de haakjes.
Ik heb er dus maar een user defined function van gemaakt, ook al omdat ik 't op een hoop plekken nodig heb:

code:

CREATE FUNCTION FullName (@FirstName VARCHAR(50), @Prefix VARCHAR(15), @LastName VARCHAR(50))
RETURNS VARCHAR(255) AS
BEGIN
  DECLARE
    @fullname VARCHAR(255)
  SET @fullname = RTRIM(@FirstName)
  IF (RTRIM(@Prefix) > '')
    SET @fullname = @fullname + ' ' + RTRIM(@Prefix)
  IF (RTRIM(@LastName) > '')
    SET @fullname = @fullname + ' ' + RTRIM(@LastName)
  SET @fullname = LTRIM(@fullname)  
  RETURN @fullname
END

[Urk]

Ik ben nu even bezig om m'n applicatie "SQL Injection Attack" proof te maken, echter vind ik wel informatie zoals in de links hierboven om dit te doen met Parameterized Queries, in het voorbeeld word echter gebruik gemaakt van C# met MS SQL server. Ik gebruik nog gewoon ASP.

Heb het volgende geprobeerd:

ASP:

InsertIntoSQL_insertgroep = "SET DATEFORMAT dmy INSERT INTO groepen (groep_groepsnaam, groep_omschrijving) " &_ 
"values (@groep_groepsnaam, @groep_omschrijving)"
Set DBCommand = Server.CreateObject("ADODB.Command")
DBCommand.ActiveConnection = DBCon
DBCommand.CommandText = InsertIntoSQL_insertgroep
DBCommand.CommandType = adCmdText
DBCommand.Parameters.Append DBCommand.CreateParameter("@groep_groepsnaam", adVarChar, adParamInput, 50, Request.Form("groep_groepsnaam"))
DBCommand.Parameters.Append DBCommand.CreateParameter("@groep_omschrijving", adVarChar, adParamInput, 50, Request.Form("groep_omschrijving"))
DBCommand.Execute

Maar dan krijg ik de foutmelding

code:

Must declare the variable '@groep_groepsnaam'.

Ik heb geen idee hoe dit komt en kan het ook nergens vinden

Nu draai ik mijn queries op de volgende methode:

ASP:

InsertIntoSQL_insertgroep = "SET DATEFORMAT dmy INSERT INTO groepen (groep_groepsnaam, groep_omschrijving, groep_toegevoegd) " &_ 
"values ('" & Trim(FixSQL(Request.Form("groep_groepsnaam"))) & "', " & CheckStringNull(Trim(FixSQL(Request.Form("groep_omschrijving"))),2) & ", '" & Now() & "')"
DBCon.Execute(InsertIntoSQL_insertgroep)

Met de functie FixSQL vervang ik gevaarlijke karakters, zoals:

ASP:

Function FixSQL(Variable)
 FixSQL = Replace(Variable,"'","''")
End Function

Echter hoe krijg ik met deze functie een dubbele quote goed in de database.
Het lukt wel met FixSQL = Replace(FixSQL,Chr(34),"\"&Chr(34)) maar dan worden er 2 karakters ingevoerd i.p.v. 1. En dat geeft weer problemen met de maximale veld grootte.

Hoe vervang ik dus een dubbele quote die goed in SQL word ingevoerd?