Hot answers tagged

62

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 ...


43

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 ...


33

This code works properly because it is: Parameterized, and Not doing any Dynamic SQL In order for SQL Injection to work, you have to build a query string (which you are not doing) and not translate single apostrophes (') into escaped-apostrophes ('') (those are escaped via the input parameters). In your attempt to pass in a "compromised" value, the ...


26

Yes, to some extent. 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 ...


23

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 ...


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

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 ...


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 ...


11

Yes, things haven't changed much in this area, you should be using quotename for any SQL server object names that are used in dynamic SQL (especially if they are supplied externally to your code). As well as SQL injection mitigation this also means your code will work correctly for non standard identifier names. The function is only appropriate for object ...


9

The attacker will get the data unencrypted. The T in TDE stands for "transparent". The user will never see encrypted data. The database transparently decrypts it when it is read from disk and transparently encrypts it when writing to disk. If your application is insecure, TDE doesn't help you plug those application security holes. You need to fix those ...


9

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 ...


9

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 ...


8

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 ...


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 ...


6

SQL Injection is hard to track from SQL Server side. Instead of looking at sql server, you should look at your web server IIS logs. Use Log Parser to parse your IIS Logs to track down the source of sql injection. e.g. logparser.exe -i:iisw3c -o:Datagrid -rtp:100 “select date, time, c-ip, cs-uri-stem, cs-uri-query, time-taken, sc-status from ...


5

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 ...


5

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 ...


5

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 ...


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

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

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 ...


4

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.


4

He seems confused. Using the same value from a local variable should be no different to using it from an input parameter. Is your colleague getting the wrong end of the stick with regard to using parametrised queries (sometimes called prepared statements) instead of full ad-hoc SQL? For example: resultSet = dbconnector.getRS('EXEC sp_foo_GET ...


4

SQL Injection is a security hole created by poorly designed dynamic SQL. Dynamic SQL being any case where a command is dynamically constructed during the execution of a program and then typically sent to SQL to execute (sometimes these commands are generated and then stored off for later execution). The ability to do this isn't a mistake, and it isn't a ...


3

Based on a simple test case I just wrote: @Test public void test() throws SQLException { PreparedStatement ps = conn.prepareStatement("SET ROLE ?"); ps.setString(1, "someuser"); ps.executeUpdate(); } I think the error you refer to is probably: org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1" Position: 10 at ...


3

The server has already been scanned with several tools and all the main password (administrator, sa of SQL Server) has been changed. If the SQL Server service account user is an administrator on the local machine, then you need to build a new machine from the ground up to replace it using long (14 character plus) cryptographically random passwords, and ...


2

You could create a function that executes SET ROLE with dynamic SQL, using format to safely insert the role identifier (%I inserts an identifier, placing double quotes around it if necessary, and escaping double quotes in the string by doubling them up if necessary). Something along the lines of CREATE FUNCTION setrole(role text) RETURNS void AS $$ BEGIN ...


2

The classic SQL injection attack attempts to extract sensitive data through the user interface. This, however, seems to be poisioning your data by appending valid HTML to it. The attacker's assumption is that you trust data coming from the DB so are likely to embed it directly into your web app's output and thus display to your users links to their site.


2

The simple matter is that you're not confusing data with command at all. The values for the parameters are never treated as part of the command, and therefore are never executed. I blogged about this at: http://sqlblog.com/blogs/rob_farley/archive/2015/02/10/sql-injection-the-golden-rule.aspx


2

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



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