Tag Info

Hot answers tagged

50

No, stored procedures do not prevent SQL injection. Here's an actual example (from an in-house app someone created where I work) of a stored procedure that unfortunately permits SQL injection: CREATE PROCEDURE [dbo].[sp_colunmName2] @columnName as nvarchar(30), @type as nvarchar(30), @searchText as nvarchar(30) AS BEGIN ...


39

SQL-Injection attacks are those where untrusted input is directly appended queries, allowing the user to effectively execute arbitrary code, as illustrated in this canonical XKCD comic. Thus, we get the situation: userInput = getFromHTML # "Robert ') Drop table students; --" Query = "Select * from students where studentName = " + userInput Stored ...


22

Yes, to some extend. Stored Procedures alone will not prevent SQL Injection. Let me first quote about SQL Injection from OWASP A SQL injection attack consists of insertion or "injection" of a SQL query via the input data from the client to the application. A successful SQL injection exploit can read sensitive data from the database, modify database data ...


15

Because '2012/12/1' in the US is 11 months after the same string date in Europe. Allowing implicit conversions means you are at the mercy of location settings. If you can name a business where 11 months is an acceptable margin of error I'll be impressed.


14

Short answer, no. The quoting trick is easily defeated by including your own closing quote and then a comment symbol to eliminate the final concatenated quote, precisely as in your example. To protect yourself from SQL injection you must use bind variables. Changing your example to SELECT * FROM table WHERE ID = :X and then binding the user's input to X ...


14

There are problems that will occur if a session with a different date format runs the code. Statement Failure DROP TABLE t1; CREATE TABLE t1 AS (SELECT sysdate mydate FROM dual WHERE 1=2); ALTER SESSION SET NLS_DATE_FORMAT = 'MON-DD-RR'; INSERT INTO t1 VALUES ('01-02-12'); * ERROR at line 1: ORA-01843: not a valid month Bad Data ...


11

It depends. With SQL functions (LANGUAGE sql), the answer is generally yes. Passed parameters are treated as values and SQL-injection is not possible - as long as you don't call unsafe functions from within and pass parameters. With PL/pgSQL functions (LANGUAGE plpgsql), the answer is normally yes. However, PL/pgSQL allows for dynamic SQL where passed ...


8

Yes, it is possible to perform an SQL injection attack without supplying quotes in the parameter. The way to do this is with an exploit to do with how numbers and/or dates are processed. You can specify at the session level what the format of a date or number is. By manipulating this you can then inject with any character. By default in the UK and US, a ...


7

String concatenation is the cause of SQL Injection. This is avoided using parametrisation. Stored procedures add an additional layer of security by enforcing invalid syntax when you concatenate, but are not "safer" if you use, say, dynamic SQL in them. So, your code above is caused by concatenation of these strings exec sp_GetUser ' x' AND 1=(SELECT ...


6

"SQL injection attacks happen when user input is improperly encoded. Typically, the user input is some data the user sends with her query, i.e. values in the $_GET, $_POST, $_COOKIE, $_REQUEST, or $_SERVER arrays. However, user input can also come from a variety of other sources, like sockets, remote websites, files, etc.. Therefore, you should really treat ...


5

Make sure the application connects to the server using a login that has been given only read permissions (give it the db_datareader role in the database to allow reading all tables), and you should be in good shape. The easiest way to prevent changing data is to ensure the user doesn't have permission to change anything. Be careful about granting execute ...


4

According to Erland's Sommarskog's article about dynamic sql, the use of parameterized queries should be the best point in fighting against sql injection. About performance, I also see no problem, because the execution plan of dynamic sql is reused since MSSQl 2005, if I remember correctly. Only in sql 2000 and older there was a problem with plan reuse. His ...


4

There is still a potential for injection here. Let's assume that: someone has the ability to create tables in this database, but not drop them that someone has the ability to call this procedure the procedure runs as an elevated user That person could call this procedure to get results from foo, but in the meantime create a table called [foo;drop table ...


4

There is nothing wrong with using dynamic SQL if you must. In fact in some circumstances it is the only option that you have. It is more of a recommendation not to use it as yes it can lead to a SQL injection if your input is not sanitized, and yes using dynamic SQL in modules that get called often can be detrimental to it's performance. I don't think ...


4

Dynamic SQL is a tool. And as a tool have some applications - for administrative works is a blessing, for example. Not so good on SP used by applications, specially if you didn't manage the parametrization of the generated code(latest versions of SQL Server reduced the problems, but still valid). I won't enter in detail here, so I'll recommend an ...


4

I’ve recently written a detailed guide on how to switch from MySQL’s utf8 to utf8mb4. If you follow the steps there, everything should work correctly. Here are direct links to each individual step in the process: Step 1: Create a backup Step 2: Upgrade the MySQL server Step 3: Modify databases, tables, and columns Step 4: Check the maximum length of ...


4

You could probably overload the data type that you are using, causing that statement to fail. Then what comes after could potentially be run. Maybe sending it in as a Unicode byte array would do the trick and get you out of that statement to another one. If there's a hole open, it'll be abused. And blocking all strings with a single quote isn't a good ...


3

Yes, this procedure is safe from SQL Injection, provided that it is called from a properly parametrized .NET application. Performance will be fine, provided that you have all the needed indexes in place.


2

Yes, this is unsafe. Try passing this: @TableName = 'FakeTableName'';DROP TABLE Foo;PRINT''You have been Pwned' One way to avoid this is to pass @TableName as a parameter to the dynamic SQL. Like so: SET @DynamicSQL = '... WHERE TABLE_NAME = @TableName' And then do this: INSERT INTO @Receiver EXEC sp_executesql @DynamicSQL, @TableName From the ...


2

User just has to enter 234' OR 1=1 -- (with the trailing space) to break this.


2

The excerpt provided above will not prevent SQL injections. Even with single quotes the input can be modified to manipulate the quotes. With quotes the query will be: SELECT * FROM table WHERE ID='234', where the input is 234 Now consider the injected input: 234'; select * from passwords;select * from table where id='234. This will create a query block of: ...


2

If direct SQL is going to be allowed, you really have to use a separate login for it if the main application connection is read/write. Not only should this login have restricted permissions as @db2 mentioned, but doing this also allows you to do things like better-manage the resources (Resource Governor) being used by this feature. Even if all that's ...


1

SQL Injection is hard to track from SQL Server side. If you want to find out who change what, then some sort of tracing should be running on your server. If no tracing (custom one implemented by your company DBA or sysadmin) is running, then you can check windows event logs sql server error logs Default Trace for objects modified. Successful and Failed ...


1

It may, yes, though it is difficult to tell without knowing what the code is doing around that point. Anywhere where you take user input and place it into ad-doc SQL an easy injection route is presented to a malicious user simply by adding ' characters into the relevant input. For instance if your code does something like: results = dbObject.RunSQL("SELECT ...


1

Have you tried executing this, both of the scripts? Because this is not a correct syntax.I will assume that the first script intended to be something like: declare @tVariable nvarchar(max) declare @stmt nvarchar(max) declare @par1 nvarchar(500); set @tVariable= 'sysusers' set @stmt = 'select * from ' +@tVariable set @par1 = null EXEC sp_executesql ...


1

Yes. By checking the parameter first, you are removing the injection vulnerability. NO - As Aaron pointed out in his answer, you could still create SQL Injection using this construct. Just to be clear, you should never concatenate SQL. Use sp_executesql's built-in parameter handling.


1

Yes it is still vulnerable. The @DatabaseName parameter can include code that terminates one command and starts another. You are creating a string based on @DatabaseName and then executing it. That is a classic SQL injection vulnerability.


1

If you really can limit to alphanumeric characters, then yes, that's fine, IF you are limiting to ANSI alphanumeric characters. In Unicode, because every character is more than one byte, many representations of alphanumeric characters are actually unsafe and could lead to injection. You will need to sanitize the data server-side and make sure the encoding ...


1

The normal way is to make sure that the various keywords from SQL aren't permitted in the string the user enters. However there are many cases to check. Quotations can be avoided by something like this: SELECT CHAR(97) + CHAR(100) + CHAR(109) + CHAR(105) + CHAR(110) Returns admin and the technique of adding characters together in that manner can be used ...


1

If you write your query in SQL you must doubled your ' in this way: select * from gsapi_articles where article_title = 'James Woods On Obama: ''This President Is A True Abomination'''



Only top voted, non community-wiki answers of a minimum length are eligible