security – secure dynamic SQL for generic search

Following a discussion on SQL injection, I wanted to put forward a proof of concept to find out if this one was really safe and protected against SQL injection or any other malicious use. For a good reference about building a dynamic search with dynamic SQL, I would probably look there.

This is supposed to be a proof of concept, not a complete working solution to illustrate how we can accept text input by users, but manipulate it as if it were set correctly.

The assumptions are as follows:

1) We do not want to execute client-side code – in theory, it could have been done in an intermediate level as an API. However, even if there was an intermediate API endpoint, it is useless if it does not correctly set the request made on behalf of the users. Moreover, doing it in SQL means that it is now generic for all clients that might need the functionality, but at the cost of poor portability. This will probably work only on Microsoft SQL Server, not on other database vendors, at least without significant changes.

2) Under no Circumstances should allow users to write dynamic SQL, either directly or indirectly. The only thing that should write dynamic SQL is our code, without the user inputs. This means that more indirection is needed to prevent user input from being part of dynamic SQL during assembly.

3) We assume that users only need to search in a single table, they want all columns, but may want to filter any column. This is simply to simplify the proof of concept – there is no technical reason not to do more, provided that the practices described in the proof of concept are rigorously followed.

Support function for data types

We first need a function to help us build a formatted data type because the sys.types do not present the information in the most user-friendly way to write a parameter. Although this may be more sophisticated, this is sufficient for the most common cases:

CREATING OR MODIFYING THE FUNCTION dbo.ufnGetFormattedDataType (
@DataTypeName sysname,
@Precision int,
@Scale int,
@MaxLength int
) RETURNS nvarchar (255)
WITH SCHEMABINDING AS
TO START
DECLARE @Suffix nvarchar (15);

SET @Suffix = CASE
WHEN @DataTypeName IN ("nvarchar", "nchar", "varchar", char, # varbinary & # 39; 39,, N & # 39; binary & # 39;)
THEN CONCAT (N '#, IIF (@MaxLength = -1, N' MAX ', CAST (@MaxLength AS nvarchar (12))), & # 39;) & # 39;)

WHEN @DataTypeName IN (N <decimal, # numeric #)
THEN CONCAT (N ', @Precision, N,, @Scale, N);

WHEN @DataTypeName IN ("datetime2", no datetimeoffset, "time")
THEN CONCAT (N & # 39 ;, @Scale, N & # 39;) & # 39;)

OTHERWISE & # 39;
END;

RETURN CONCAT (@DataTypeName, @Suffix);
END;

Main dynamic search procedure

With the function, we can then build our main procedure to create dynamic SQL to support generic search:

CREATE OR MODIFY THE PROCEDURE dbo.uspDynamicSearch (
@TableName sysname,
@ParameterXml xml
) AS
TO START
DECLARE @stableName sysname,
@stableId int,
@err nvarchar (4000)
;

TO SELECT
@stableName = o.Name,
@stableId = o.object_id
AS sys.objects
WHERE o.name = @tableName;

IF @stableName IS NULL
OR @stableId IS NULL
TO START
SET @err = N does not include a specified table name. & # 39 ;;
THROW 50000, @err, 1;
RETURN -1;
END;

WITH BaseData AS (
TO SELECT
x.value (N @ @ Name, # sysname & # 39;) AS ParameterName,
x.value (N @ Value, # narcarc (MAX)) AS ParameterValue
FROM @ ParameterXml.nodes (N / # / l / p) AS t (x)
)
TO SELECT
ROW_NUMBER () OVER (ORDER BY (SELECT NULL)) AS ID,
c.name AS ColumnName,
d.ParameterValue AS ParameterValue,
c.user_type_id AS DataTypeId,
t.name AS DataTypeName,
c.max_length AS MaxLength,
Precision AS Precision,
Scale AS on the scale,
dbo.ufnGetFormattedDataType (t.name, c.precision, c.scale, c.max_length) AS ParameterDataType
INTO #ParameterData
DE BaseData AS d
INNER JOIN sys.columns AS c
ON d.ParameterName = c.name
INNER JOIN sys.types AS t
ON c.user_type_id = t.user_type_id
WHERE c.object_id = @stableId;

DECLARE @Sql nvarchar (MAX) = CONCAT (N / # 39; SELECT * FROM & # 39 ;, @stableName);

IF EXISTS (
SELECT NULL
FROM #ParameterData
)
TO START
DECLARE @And nvarchar (5) = N & # 39 ;;

SET @Sql + = CONCAT (WHERE & # 39; STUFF ((
TO SELECT
CONCAT (@Et, QUOTENAME (d.ColumnName), N = = P, d.Id)
DE #ParameterData AS d
FOR XML PATH (N '#);
), 1, LEN (@And), N '(& # 39;));

DECLARE @Params nvarchar (MAX) = CONCAT (N / A DECLARE & STUFF, (
TO SELECT
CONCAT (N, @ P, d.Id, N, d.ParameterDataType, N = (SELECT CAST (d.ParameterValue AS), d.ParameterDataType, N 'FROM #ParameterData AS d WHERE d.Id =', d.Id, N ')
DE #ParameterData AS d
FOR XML PATH (N '#);
), 1, 2, N ', & nbsp; & nbsp; & nbsp;);

SET @Sql = @Params + @Sql;
END;

EXEC sys.sp_executesql @Sql;
END;

Analysis

Let's review the procedures in part to develop the rationale behind the design, starting with the parameters.

@TableName sysname,
@ParameterXml xml 

The name of the table goes without saying, but we ask users to provide their search conditions as an XML document. It is not necessary that it be an XML document. JSON would also work (provided you are using a recent version of SQL Server). The fact is that it must work in a well-defined format with native support for content analysis. An example of XML might look like this:


  

XML is basically a (l) ist of arameters (p) in name-value pairs.

We have to validate both parameters. The first is easy to do:

TO SELECT
@stableName = o.Name,
@stableId = o.object_id
AS sys.objects
WHERE o.name = @TableName;

Because we do do not want user input to go directly into dynamic SQL, we use a separate variable, @stableName which would have the same value as the @Name of the table but only if the user is not malicious and tried to sneak in extra characters. Since we filter through the sys.objects, which implicitly applies the SQL Server identification rules and thus validates the validity of the entry.

For the parameters, we need more work, so we have to load in a temporary table.

WITH BaseData AS (
TO SELECT
x.value (N @ @ Name, # sysname & # 39;) AS ParameterName,
x.value (N @ Value, # narcarc (MAX)) AS ParameterValue
FROM @ ParameterXml.nodes (N / # / l / p) AS t (x)
)
TO SELECT
ROW_NUMBER () OVER (ORDER BY (SELECT NULL)) AS ID,
c.name AS ColumnName,
d.ParameterValue AS ParameterValue,
c.user_type_id AS DataTypeId,
t.name AS DataTypeName,
c.max_length AS MaxLength,
Precision AS Precision,
Scale AS on the scale,
dbo.ufnGetFormattedDataType (t.name, c.precision, c.scale, c.max_length) AS ParameterDataType
INTO #ParameterData
DE BaseData AS d
INNER JOIN sys.columns AS c
ON d.ParameterName = c.name
INNER JOIN sys.types AS t
ON c.user_type_id = t.user_type_id
WHERE c.object_id = @stableId;

In addition to validating the column names we want to use for the filters, we collect metadata from the sys.columns and sys.types. Note that the XML itself can not be used to tell us what types of data the user wants to use. Since this would be a malicious attack vector, we need to rely on the catalog view information, accepting only the values ​​directly from the user provided XML.

Note the ROW_NUMBER () generate the identifiers of the parameters. This is important, as we will see later.

DECLARE @Sql nvarchar (MAX) = CONCAT (N / # 39; SELECT * FROM & # 39 ;, @stableName);

We build our first part of dynamic SQL. We assume that it is ok to allow users to select the entire table, although it may be dickish if there are a lot of records. In a complete solution, it might be safer to have a TOP 100 or something like that.

In the future, we will assume that we have a set of parameters that we need to filter on.

SET @Sql + = CONCAT (WHERE & # 39; STUFF ((
TO SELECT
CONCAT (@Et, QUOTENAME (d.ColumnName), N = = P, d.Id)
DE #ParameterData AS d
FOR XML PATH (N '#);
), 1, LEN (@And), N '(& # 39;));

Here we abuse the FOR XML PATH to provide a concatenation of the filter predicate for the OR clause. Using the XML example above, the result would have looked like OR [First Name] = @ P1 AND [Last Name] = @ P2. Note the horrible name of the columns, with spaces, to show the value of QUOTENAME to make sure that even in a bad database schema, we can avoid getting an error with an iffy identifier.

DECLARE @Params nvarchar (MAX) = CONCAT (N / A DECLARE & STUFF, (
TO SELECT
CONCAT (N, @ P, d.Id, N, d.ParameterDataType, N = (SELECT CAST (d.ParameterValue AS), d.ParameterDataType, N 'FROM #ParameterData AS d WHERE d.Id =', d.Id, N ')
DE #ParameterData AS d
FOR XML PATH (N '#);
), 1, 2, N ', & nbsp; & nbsp; & nbsp;);

This is what the user can do more closely – we would read from the same temporary table that we created and assign to a parameter we create ourselves, with a THROW. Note that we could have used a TRY_CAST to avoid a runtime error, but I would say that an error should occur if users type incorrectly. In a complete solution, the procedure could be encapsulated in a TRY / ATTACH block to delete the error message one way or another.

Using the XML example above again, it would give something like this:

DECLARE @ P1 varchar (100) = SELECT CAST (d.ParameterValue AS varchar (100)) FROM #ParameterData AS d WHERE d.Id = 1;

Note that we did not even use the name provided by the users. we used a numerical identifier concatenated by our own code. In addition, the code reads from the temporary table and THROW this in the parameter we want it to be. This allows us to more easily manage different types of data for different parameters that users can send us, but without concatenating the values ​​they provide to our dynamic SQL.

Once we have that, we concatenate the assignments to the @sql and execute it:

EXEC sys.sp_executesql @Sql;

Note that we have not used the @param parameter of the sp_executesql – There are no parameters that we can really transmit because they are in a temporary table. That's why we used assignments in dynamic SQL to move the user's input from an XML document to a parameter in dynamic SQL.

Can it be broken?

As mentioned, the discussion about SQL injection made me wonder if I had perhaps forgotten something or let it be assumed that the malicious user could still bypass the layers of the ################################################################################### 39; indirection that I've set up and inject his ugly little SQL?

encryption – does the combination of TOR and VPN provide total security and anonymity?

I wish absolute anonymity online, without anyone being able to locate me or the sites I've visited, TOR referrals are secure. If you use VPN, would that mean that if someone wanted to, he could follow the output and return your vpn to you or are you totally safe? does it result in 100% security, are you safe from the United States Government of America or not?

Users are not allowed to reuse old passwords for security reasons. How to mitigate this painful point?

I work in a health care start-up, which is very strict about its connection guidelines, largely because protecting the information of patients and providers is a vital need in this sector. But if our rigor meets an important need, it can cause a lot of headaches to our suppliers. Much of this frustration comes from the fact that vendors, when changing their passwords, are not allowed to reuse any of their last 12 passwords. And they have trouble remembering their old passwords, and there is no systematic / secure way to provide them with this information. The worst thing is that they are also required to change their password every 90 days. Thus, between the cognitive burden necessary to remember all their recent passwords and the frequency with which they have to update their passwords, the providers are fed up and abandon the platform with frustration or skepticism. They rely heavily on customer support, flooded with requests to change vendor passwords for them.

That said, has anyone ever encountered the problem of the "previous password"? And if so, what approaches did you use to mitigate it? Thank you for your help!

(and a FYI, I asked my security team if there was any leeway on the frequency of PW's change, as well as on the criteria for change, but they said that 39, was practically engraved in marble)

security – Long Term BTC Investment

My question is: what is the safest way to buy and keep bitcoins in the long run?
And I mean, 10-20 years, either a lump sum or an average dollar cost, is a paper wallet the solution? Or maybe a cold wallet? I am looking for something very safe that will probably be there again in 20 years

[WTS] Web Hosting Plans # 1 | DailyRazor.com

Do you need a reliable and ultra fast web hosting on Linux and Windows?

DailyRazor.com is a professional web hosting provider and other web services such as domain names, ecommerce, web design, search engine optimization and marketing, charts and consulting for small businesses and individuals around the world.

We have more than 15 years of combined experience in Java, .NET, PHP, Ruby, CGI, network administration, systems integration and related technologies to support the & # 39; Critical hosting for applications developed on these platforms. In addition, we offer web hosting packages to support common technologies such as Perl, Python, and Ruby, as well as databases for the ever-growing and popular Microsoft Access, Microsoft SQL Server, MySQL, and MySQL databases. PostgreSQL.

Our features:

  • Professional support 24/7
  • 30-day money back guarantee
  • 99.9% uptime guarantee
  • Fast and fast server infrastructure
  • 1-Click App Install (hundreds of choices)
  • Award-winning Web Hosting
  • More than 15 years of experience in Linux and Windows hosting

Our Web hosting plans are designed to help your website succeed! We offer an instant installation software called Softaculous that allows you to automatically install over 276 open-source scripts at the click of a mouse. You can install WordPress, Joomla, Opencart, PrestaShop, phpBB, Drupal, SMF, MyBB, Magento, Dolphin, Open Blog, TextPattern, LifeType, etc. with one click!

Note: Each hosting plan comes with: disk space, bandwidth, email accounts, FTP accounts as much as you need + FREE domain name.

Dailyrazor also offers Tomcat hosting, Joomla hosting, OpenCart hosting, vBulletin hosting Solutions!

Use the code: SUPERSHARED and get up to 60.40% discount on all accommodation plans!

Entrance
1 site / domain
10 databases
Domain FREE Domain
FREE Website Builder
FREE SSL security
CPanel Control Panel
$ 3.15 / month 60.40% discount (cost $ 7.95) – ORDER NOW
Click here for more plans and details: https://www.dailyrazor.com/web-hosting/

Our ASP.NET top hosting plans are designed to help your website succeed!
Use the code: SUPERSHARED and get up to 60.40% discount on all accommodation plans!

Entrance
1 site / domain
10 databases
Domain FREE Domain
FREE Website Builder
FREE SSL security
CPanel Control Panel
$ 3.94 / month Discount of 60.40% (was $ 9.95) – ORDER NOW
Click here for more plans and details: https://www.dailyrazor.com/asp-net-hosting/

Our Ultimate Reseller Hosting plans are designed to help your website succeed!
Use the code: SUPERRESELL and get up to 10% DISCOUNT on all reseller hosting plans!

Bronze
Host unlimited domains / websites
25 GB of disk space
250 GB transfer / bandwidth
WHM / cPanel Control Panel
$ 11.65 / month 10.00% discount (was $ 12.95) – ORDER NOW
Click here for more plans and details: https://www.dailyrazor.com/reseller-hosting/

Our ColdFusion Expert Hosting plans are designed to help your website succeed!
Use the code: CFPRIMO and get up to 50% DISCOUNT on all ColdFusion hosting plans!

CF-One
1 site / domain
1 MS SQL database
5 MySQL databases
Free domain name
Plesk Control Panel
$ 7.98 / month 50% discount ($ 15.95) – ORDER NOW
Click here for more plans and details: https://www.dailyrazor.com/coldfusion-hosting/

We also offer a 1-Click Application installer integrated into our Plesk control panel, which allows you to automatically install tons of open-source scripts at the click of a mouse. You can install WordPress, Joomla, Opencart, PrestaShop, phpBB, Drupal, SMF, MyBB, Magento, Dolphin, Open Blog, TextPattern, LifeType, etc. with one click!

Our guarantee:
Try one of our FREE hosting packages for 30 days! We will let our quality service speak for us and if you are not satisfied with our service, simply contact us to cancel before or the 30th day and we will be happy to refund your deposited funds without any question asked!

If you have any questions, do not hesitate to contact our support: Send the ticket

Like DailyRazor THE FACEBOOK PAGE
Follow DailyRazor via TWITTER ACCOUNT

.

security – How to export an EdgeScan vulnerability report with the listed port and protocol

I'm trying to collect a report from information on live.edgescan.com

I notice that I can export a csv report of the detected vulnerabilities on the page https://live.edgescan.com/app#/vulnerabilities. This does not include the port and protocol of each vulnerability with the host information. There is a drop-down tab under each item on the Vulnerabilities page, but these do not appear as columns in the report or page.

How can I access these and add them to a report?

java – Do I need to add a security policy to use EclipseLink or Hibernaty EntityManager?

I am a book on JPA. It uses EclipseLink. When I'm going to configure Entity MAnager, I receive a security exception when the library does not have permission to read a system property, same thing with Hibernate. The book does not mention anything about creating a new security policy.

I was tired of understanding the problem, so it seems like I should create a security policy and add the system properties that eclipseLink wants to set and read. But thinking that it seemed wrong to me, I tried to install Hibernate Building by deploying war and connecting to the database. Even Java security problem does not allow properties to be read in these libraries.

The main thing I want to know is it normal to have to make an exception to the security policy to be able to use Entity Manager?

[WTS] Kvchosting.net solid and decent with quality support.

This is a discussion on Kvchosting.net solid and decent with quality support. in the Webmaster Marketplace forums, part of the enterprise category; KVC Hosting was launched in 2010 for the sole purpose of creating a host society that is affordable for everyone. …

.

[WTS] Legionbox has well-balanced servers with high availability and low prices in Europe.

Legionbox is the leading provider of VPS and dedicated servers, created and running successfully for those who consistently need high performance, reliability, stability and server security!

Linux Virtual Private Servers

With Legionbox virtual server, RAM, storage and capacity without overload are provided to you.

Each VPS comes with:

– unmeasured bandwidth;
– Instant installation (no downtime);
– Free panel ISPmanagerLite 4;
– 7 day money back guarantee;
– Payment Options: Paypal, Bitcoin, WebMoney and major credit cards;
– High security (level III security level).

Xen VPS (most popular plans)

Choose one of the options: CentOS, Ubuntu, Debian, Fedora or CentOS on cPanel.
Available locations: United States, Los Angeles, Switzerland, Zurich, Germany, Nuremberg, Russia, Moscow

XenVps0.5
CPU: 1хE5-2680
RAM: 0.5 GB
Disk space: 15 GB
$ 7 / month
ORDER

XenVps1
CPU: 2хE5-2680
RAM: 1 GB
Disk space: 25 GB
$ 11.99 / month
ORDER

XenVps2
CPU: 2хE5-2680
RAM: 2 GB
Disk space: 50 GB
$ 23.99 / month
ORDER

Need another configuration for XEN VPS? Check out more options here: https://legionbox.com/virtual-servers/

SSD VPS (most popular plans)

Choose one of the options: CentOS, Ubuntu, Debian, Fedora or Windows.
Available locations: United States, Los Angeles, Switzerland, Zurich, Germany, Nuremberg.

SSDVPS2
CPU: 1хE5-2680
RAM: 2 GB
Disk space: 20 GB
$ 9.95 / month
ORDER

SSDVPS4
CPU: 2хE5-2680
RAM: 4 GB
Disk space: 30 GB
$ 19.95 / month
ORDER

Need another configuration for SSD VPS? Check out more options here: https://legionbox.com/virtual-servers/

On Legionbox, you can also get Windows VPS from $ 11.99 / month >>>>

WinVps1
CPU: 2хE5-2680
RAM: 1 GB
Disk space: 25 GB
$ 11.99 / month
ORDER

WinVps4
CPU: 2хE5-2680
RAM: 4 GB
Disk space: 80 GB
$ 44.99 / month
ORDER

Check here for more Windows VPS

Need a dedicated server? Check out the best and fastest Windows and Linux dedicated servers here!

Have a question?
Do not hesitate to open a ticket

https://legionbox.com/

.

Has the security of the Cores Bitcoin Encryption Portfolio ever been bypassed?

Every two or three years, there seems to be a feat in Trezor that allows people to bypass security. I was wondering if it was the same for bitcoin core wallet encryption?