Contents tagged with MS SQL
today I want to write a simple note on how to make stored procedures in MS SQL server which are protected from SQL injections.
Below goes example of SQL stored procedure, that is vulnerable to SQL injection attacks:
-- Bad code, don't use it ever
CREATE PROCEDURE SearchCustomers
DECLARE @query VARCHAR(100)
SET @query = 'SELECT * FROM Customer WHERE NAME LIKE ''%' + @searchCust + '%'''
Why it is bad? Because if somebody passes into @searchCust value or 1=1 --- then SQL will return all customers. If to add a bit more creativity it's possible to get from that database plenty of information.
If you wonder, … more
today I want to document one hack that preserved me plenty of space on my hard drive.
Recently I've tried to make back up of production database and was shocked to see that it had almost 200 Gb on my hard drive. So it made me wonder how can I have smaller backups.
And here is the solution.
In Microsoft SQL Server Management Studio click on database, and choose new query
enter there following command: sp_configure 'backup compression default', 1
then this: reconfigure WITH OVERRIDE;
After such manipulations I've got back up that had only 27 Gb. more
today I want to write some notes about hierarchy research in ms sql database.
Imagine you have following structure in your table
id | rootID | some additional fields
1 | 1 | ----
2 | 1 | ----
3 | 2 | ----
4 | 2 | ----
5 | 2 | ----
and image that you need to get info like who is below 1.
for this purpose following sql can be useful:
-- wrap up tree selection into tvf
create function HierarchyFunction(@ … more
some sql notes:
dbcc freeproccache; this one will clean plan of cached of sql.
Another note of how to find Sql code and plan by id from dm_exec_cached_plans
create function SqlAndPlan(@handle varbinary(max))
return select sql.text, cp.usecounts,cp.cacheobjtype,
sys.dm_exec_sql_text(@handle) as sql cross join
sys.dm_exec_query_plan(@handle) as qp
join sys.dm_exec_cached_plans as cp
on cp.plan_handle = @handle;
then you can use it in the following way:
select * from SqlAndPlan(0x06650D007852A00B40613C82000000000000000000000032)
And in order to see sql and plan simultanously you can use … more
some MS SQL internals.
Imagine, you want to see what MS SQL cached. How you can do it? One of the ways is to look into sys.dm_exec_cached_plans.
For example like this:
select * from sys.dm_exec_cached_plans
this code will return to you all what is cached in your MS-SQL database. Most probably result will puzzle you. What to do with all of it?
Take look at the screenshot :
the area of interest for us is column plan_handle.
You can use it for another sql request. Like this:
select * from sys.dm_exec_query_plan(0x06000C002D51D1304021F8AE000000000000000000000000);
if you click at column with xml you'll see the following picture:
How to interpret those … more
Few days ago I faced following challenge. SQL profiler gave me ugly SQL, which was unreadable for my eyes.
For example like this:
exec sp_executesql N'SELECT APAddress.AddressID, APAddress.VendorID, APAddress.VendorAddressID, APAddress.IsDefaultAddress, APAddress.RevisionID, APAddress.AddressLine1, APAddress.AddressLine2, APAddress.AddressLine3, APAddress.City, APAddress.CountryID, APAddress.State, APAddress.PostalCode, APAddress.IsValidated, APAddress.tstamp, APAddress.CreatedByID, APAddress.CreatedByScreenID, APAddress.CreatedDateTime, APAddress.LastModifiedByID, APAddress.LastModifiedByScreenID, APAddress.LastModifiedDateTime FROM APAddress APAddress WHERE (APAddress.CompanyID = 2) AND … more