Authorjoe

PHP: Check URL parameters

<?php
// ipaddress
$ipaddress = $_SERVER[‘REMOTE_ADDR’];
echo “ipaddress: “. $ipaddress. “<br/><br/>”;

// complete page url
$link = “http://$_SERVER[HTTP_HOST]$_SERVER[REQUEST_URI]”;
echo “link: “. $link. “<br/><br/>”;

// page url without parameters
$linkParts = explode(‘?’, $link);
$mypage = $linkParts[0];
echo “mypage: “. $mypage. “<br/><br/>”;

echo “=============================<br/><br/>”;

// parameter string from the url
$pageparms = $_SERVER[“QUERY_STRING”];
$pageparms = strtolower($pageparms);
echo “pageparms: “. $pageparms. “<br/><br/>”;

echo “=============================<br/><br/>”;

// number of parameters specified
$parmParts = explode(‘&’, $pageparms);
$numparts = count($parmParts);
echo “numparts: “. $numparts. “<br/><br/>”;

// 1st parameter if specified
if (0 < $numparts) {
echo “parm1: “. $parmParts[0]. “<br/><br/>”;
}

// 2nd parameter if specified
if (1 < $numparts) {
echo “parm2: “. $parmParts[1]. “<br/><br/>”;
}

// 3rd parameter if specified
if (2 < $numparts) {
echo “parm3: “. $parmParts[2]. “<br/><br/>”;
}

// 4th parameter if specified
if (3 < $numparts) {
echo “parm4: “. $parmParts[3]. “<br/><br/>”;
}

// 5th parameter if specified
if (4 < $numparts) {
echo “parm5: “. $parmParts[4]. “<br/><br/>”;
}

// 6th parameter if specified
if (5 < $numparts) {
echo “parm6: “. $parmParts[5]. “<br/><br/>”;
}

echo “=============================<br/><br/>”;

// see if cow was specified
if ($parmParts[2] == “cow”) {
echo “Found the cow <br/>”;
} else {
echo “Still looking for the cow <br>”;
}

?>

SQL Search all tables for text

I was looking for a string inside of a table in a massive database.  I was attempting to do this by trial-and-error until I ran across this method which does the searching for me.  This script searches all the text/string type fields in every table in the selected database.  Simply specify the “StringToFind”.

Declare @StringToFind VARCHAR(200), @Schema sysname, @Table sysname, @FullTable int, @NewMinID int, @NewMaxID int,
@SQLCommand VARCHAR(8000), @BaseSQLCommand varchar(8000), @Where VARCHAR(8000), @CountCheck varchar(8000) , @FieldTypes varchar(8000),
@cursor VARCHAR(8000), @columnName sysname, @SCn int, @SCm int
Declare @TableList table (Id int identity(1,1) not null, tablename varchar(250))
Declare @SQLCmds table (id int identity(1,1) not null, sqlcmd varchar(8000))
Declare @DataFoundInTables table (id int identity(1,1) not null, sqlcmd varchar(8000))

— Settings

SET @StringToFind = ‘.NET Framework 4.7.2’
SET NOCOUNT ON
SET @StringToFind = ‘%’+@StringToFind+’%’

— Gathering Info

if ((select count(*) from sysobjects where name = ‘tempcount’) > 0)
drop table tempcount

create table tempcount (rowsfound int)
insert into tempcount select 0

— This section here is to accomodate the user defined datatypes, if they have
— a SQL Collation then they are assumed to have text in them.
SET @FieldTypes = ”
select @FieldTypes = @FieldTypes + ”” + rtrim(ltrim(name))+”’,’ from systypes where collation is not null or xtype = 36
select @FieldTypes = left(@FieldTypes,(len(@FieldTypes)-1))

insert into @TableList (tablename)
select name from sysobjects
where xtype = ‘U’ and name not like ‘dtproperties’
order by name

— Start Processing Table List

select @NewMinID = min(id), @NewMaxID = max(id) from @TableList

while(@NewMinID <= @NewMaxID)
Begin

SELECT @Table = tablename, @Schema=’dbo’, @Where = ” from @TableList where id = @NewMinID

SET @SQLCommand = ‘SELECT * FROM ‘ + @Table + ‘ WHERE’
— removed ‘ + @Schema + ‘.

SET @cursor = ‘DECLARE col_cursor CURSOR FOR SELECT COLUMN_NAME
FROM [‘ + DB_NAME() + ‘].INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = ”’ + @Schema + ”’
AND TABLE_NAME = ”’ + @Table + ”’
AND DATA_TYPE IN (‘+@FieldTypes+’)’
–Original Check, however the above implements user defined data types –AND DATA_TYPE IN (”char”,”nchar”,”ntext”,”nvarchar”,”text”,”varchar”)’

EXEC (@cursor)

SET @FullTable = 0
DELETE FROM @SQLCmds

OPEN col_cursor
FETCH NEXT FROM col_cursor INTO @columnName

WHILE @@FETCH_STATUS = 0
BEGIN

SET @Where = @Where + ‘ [‘ + @columnName + ‘] LIKE ”’ + @StringToFind + ””
SET @Where = @Where + ‘ OR’

–PRINT @Table + ‘|’+ cast(len(isnull(@Where,”))+len(isnull(@SQLCommand,”)) as varchar(10))+’|’+@Where

if (len(isnull(@Where,”))+len(isnull(@SQLCommand,”)) > 3600)
Begin
SELECT @Where = substring(@Where,1,len(@Where)-3)
insert into @SQLCmds (sqlcmd) select @Where
SET @Where = ”
End

FETCH NEXT FROM col_cursor INTO @columnName
END

CLOSE col_cursor
DEALLOCATE col_cursor

if (@Where <> ”)
Begin
SELECT @Where = substring(@Where,1,len(@Where)-3)
insert into @SQLCmds (sqlcmd)
select @Where –select @Table,count(*) from @SQLCmds
End

SET @BaseSQLCommand = @SQLCommand

select @SCn = min(id), @SCm = max(id) from @SQLCmds
while(@SCn <= @SCm)
Begin

select @Where = sqlcmd from @SQLCmds where ID = @SCn

if (@Where <> ”)
Begin

SET @SQLCommand = @BaseSQLCommand + @Where
SELECT @CountCheck = ‘update tempcount set rowsfound = (select count(*) ‘+ substring(@SQLCommand,10,len(@SQLCommand)) + ‘)’
EXEC (@CountCheck)

if ((select rowsfound from tempcount) > 0)
Begin
PRINT ‘— [‘+cast(@NewMinID as varchar(15))+’/’+cast(@NewMaxID as varchar(15))+’] ‘+@Table + ‘ ———————————-[FOUND!]’
–PRINT ‘— [FOUND USING:] ‘ +@SQLCommand
insert into @DataFoundInTables (sqlcmd) select @SQLCommand
EXEC (@SQLCommand)
update tempcount set rowsfound = 0
End
else
Begin
PRINT ‘— [‘+cast(@NewMinID as varchar(15))+’/’+cast(@NewMaxID as varchar(15))+’] ‘+@Table
End
End

SET @SCn = @SCn + 1
End

set @NewMinID = @NewMinID + 1
end

if ((select count(*) from sysobjects where name = ‘tempcount’) > 0)
drop table tempcount

/*

This will now return all the sql commands you need to use

*/

select @NewMinID = min(id), @NewMaxID = max(id) from @DataFoundInTables

if (@NewMaxID > 0)
Begin
PRINT ‘ ‘
PRINT ‘ ‘
PRINT ‘—————————————–‘
PRINT ‘———– TABLES WITH DATA ————‘
PRINT ‘—————————————–‘
PRINT ‘ ‘
PRINT ‘We found ‘ + cast(@NewMaxID as varchar(10)) + ‘ table(s) with the string ‘+@StringToFind
PRINT ‘ ‘

while(@NewMinID <= @NewMaxID)
Begin

select @SQLCommand = sqlcmd from @DataFoundInTables where ID = @NewMinID

PRINT @SQLCommand

SET @NewMinID = @NewMinID + 1
End

PRINT ‘ ‘
PRINT ‘—————————————–‘

End

SQL Delete duplicate rows based on column

To remove duplicate rows, use a CTE and partition using the data you want to check for duplicates to assign a row_number against. Then remove any where the row_number > 1 (removing multiple duplicates)

WITH CTE AS
(SELECT ROW_NUMBER() OVER
(PARTITION BY computername ORDER BY ( SELECT 0)) RN
FROM temp_devicerefresh)
DELETE FROM CTE WHERE RN > 1

Permanently remove IE from the taskbar

To permanently remove Internet Explorer from the taskbar in Windows 10, edit the file

%LOCALAPPDATA%\Microsoft\Windows\Shell\LayoutModification.xml

Remove the following line from the file.

<taskbar:DesktopApp DesktopApplicationLinkPath="%APPDATA%\Microsoft\Windows\Start Menu\Programs\Accessories\Internet Explorer.lnk" />

Get List of Files In a Directory

This example shows how to get a list of all executables in a specified directory.  The System.IO reference must be imported.

Imports System.IO

Dim d As DirectoryInfo = New DirectoryInfo("C:\")

For Each File As FileInfo In d.GetFiles("*.exe")
  Console.WriteLine(File.Name)
Next