For conditional-error-based SQLi, instead of writing 1/0 directly like MYSQL, we need to write to_char(1/0) for Oracle, what is the reason behind this? Example, Oracle: SELECT CASE WHEN (YOUR-CONDITION-HERE) THEN TO_CHAR(1/0) ELSE NULL END FROM dual Microsoft: SELECT CASE WHEN (YOUR-CONDITION-HERE) THEN 1/0 ELSE NULL END Reference:

I was testing efficacy of a paid **Database security solution** which has the ability to detect and block **SQLi attack**. For the testing purpose I have tried the following query against a **PostgreSQL** database through this tool which act as a proxy. select * from test where id=1 or 1=1; For this query the tool were able to detect and block. But then I tried another one select * from test where id=1 or true; But this time the query got infiltrated and got the full table data as result. Isn't it a classical example of SQLi? What type of queries are actually considered as SQLi which I can test against the tool?

**Problem** I am well aware that SQL is a declarative language and does not enforce short-circuiting. Oracle, on the other hand, explicitly states that short-circuiting takes place for CASE statements. I have the following example:

-- Query 1
SELECT 
    CASE 
        WHEN ASCII(SUBSTR(test_texts, 5, 1)) < 110 THEN 1 
        ELSE 0 
    END 
FROM test_data_types ORDER BY ID OFFSET 1 ROWS FETCH FIRST 1 ROWS ONLY;

-- Query 2
SELECT 
    CASE 
        WHEN ASCII(SUBSTR(test_texts, 5, 1)) < 110 THEN 1
        ELSE 0/0    -- zero division
    END 
FROM test_data_types ORDER BY ID OFFSET 1 ROWS FETCH FIRST 1 ROWS ONLY;

Query1 returns 1 because the 5th character of the row ('world') is 'd' with ASCII value 100 but Query2 raises a zero division error, meaning the short-circuiting did not take place. I assumed this was because the expression 0/0 is constant and optimization kicked is, however, expression ln(ora_hash(test_texts) - 10), which is equivalent to ln(0), throws an error unconditionally as well. Hence, eager evaluation of constant expressions is not the (sole) reason. Surprisingly enough, short-circuiting does take place when I increase the upper bound to 120, i.e., ASCII(...) < 120. Again, the selected letter is 'd' with ASCII value of 100, which is less than both 110 and 120, but only 120 works for some reason. I really can't wrap my mind around this. **Additional Context** I am doing research on blind SQL injection optimization so I'm limited to SELECT queries. Also, the error forcing is intentional, I'm just looking for a way to make it conditional. Lastly, I'm using Oracle Database 23ai Free. **Setup for Reproduction**

CREATE TABLE test_data_types (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  test_texts CLOB
);

INSERT INTO test_data_types (test_texts) VALUES ('hello');
INSERT INTO test_data_types (test_texts) VALUES ('world');
INSERT INTO test_data_types (test_texts) VALUES ('hello world');
INSERT INTO test_data_types (test_texts) VALUES ('hello world');
INSERT INTO test_data_types (test_texts) VALUES ('hello world');
INSERT INTO test_data_types (test_texts) VALUES ('hello world');
INSERT INTO test_data_types (test_texts) VALUES ('hello world');
INSERT INTO test_data_types (test_texts) VALUES ('hello world');
INSERT INTO test_data_types (test_texts) VALUES ('hello world');
INSERT INTO test_data_types (test_texts) VALUES ('hello');

COMMIT;

**Question(s)** 1) Why is short-circuiting not respected here? 2) If it's is due to optimization, are there some expressions (e.g., heavy computation) that are evaluated last? 3) As a last resort, are there some other SELECT constructs that produce the desired behavior (conditional errors)? **TL;DR of the Bellow Discussion** The issue is not that CASE short-circuiting is violated, but that the SELECT part has precedence over the OFFSET and FETCH parts, leading to evaluation of the first row ("hello") which triggers the zero division.

I have a MariaDb/PHP application used for searching biological names (Latin/Swedish) from a large scientific database. The only user textual input is to provide a (partial) name in a textfield, read by PHP and inserted in a SQL query (abbreviated): Select ... from Database.table x WHERE Condition AND MATCH (x.scientific_name) AGAINST ('"full name"' IN BOOLEAN MODE) ; or Select ... from Database.table x WHERE Condition AND x.scientific_name LIKE '%partial_name%' AND !isnull(x.scientific_name ; These queries return 0 or more results from the database.table, defined by: scientific_name varchar(255) I am presently not using parameterized queries (do I have to?) (MariaDb ver. 11.6.2, PHP ver. 8.2, Debian 12 server with Apache2) EDIT: PHP code: ``` //User input: //returns srcfield to a PHP var => $safein1 via js: function fonblur1() { var x = document.getElementById("srcfield"); ... } //A large amount of various non-text input is used to build the dynamic SQL, e.g: search exact/whole name //Dynamic SQL (untranslated code): $sqlsrc = "SELECT f.TaxonId,f.Taxonkategori,f.Vetenskapligt_namn0, f.URL_till_taxoninformation AS URL_till_taxoninformation, d.Familj AS Familj, d.Slakte AS Slakte, d.TaxonId AS TaxonId2, t.taxid, 1 AS accept , 'nosyn' AS syn concat(d.Rike,' -› ', d.Fylum,' -› ',IFNULL(d.Klass, '~')) AS grupp, FROM namndata.ftextsrc f JOIN namndata.taxonid0 t using (Taxonkategori) JOIN namndata.taxon0_T d USING (TaxonId) WHERE f.Vetenskapligt_namn0 LIKE '%" . $safein1 . "%' " . " AND d.TaxonStatus='Accepterat' AND d.".$roww2['Taxonkategori'] ." = '".$roww2['Vetenskapligt_namn']."' " . $TxIdf . " order by grupp, d.radnr, f.Vetenskapligt_namn0 LIMIT 0,2500;"; // Actual code, sent to MariaDb: SELECT f.TaxonId,f.Taxonkategori,f.Vetenskapligt_namn0, f.URL_till_taxoninformation AS URL_till_taxoninformation, d.Familj AS Familj, d.Slakte AS Slakte, 'nosyn' AS syn, d.TaxonId AS TaxonId2, t.taxid, 1 AS accept, concat(d.Rike,' -› ', d.Fylum,' -› ',IFNULL(d.Klass, '~')) AS grupp FROM namndata.ftextsrc f JOIN namndata.taxonid0 t using (Taxonkategori) JOIN namndata.taxon0_T d USING (TaxonId) WHERE MATCH (f.Vetenskapligt_namn0) AGAINST ('"carnivora"' IN BOOLEAN MODE) AND d.TaxonStatus='Accepterat' AND d.Fylum = 'Chordata' order by grupp, d.radnr, f.Vetenskapligt_namn0 LIMIT 0,2500;

I’m currently debating with a colleague whether the following (pseudo) code is vulnerable to SQL injection (SQL Server):

database.BeginTransaction();
String userId = dto.UserId;
String firstQuery = "select * from users where userid='" + userId + "'"; 
ResultSet rs = database.executeQuery(firstQuery); 
// Use the first entry in the result set as a user with 10 columns
rs.next();
User user = new User(); 
user.Username = rs.getString(2); 
... 
// Character-wise comparison of users password with dto.Password, if incorrect, unauthorized is returned here

// Now the second query, which doesn’t need a return but must run successfully
String secondQuery = "select * from settings where userid='" + userId + "'"; 
ResultSet rs = database.executeQuery(secondQuery); 
// Use the first entry in the result set as settings with 40 columns
rs.next();
Settings settings = new Settings(); 
settings.DarkMode = rs.getBoolean(2); 
...
database.CommitTransaction();
// catch block here

In reality, this code looks quite bad. I wanted to prove to my colleague that it’s vulnerable to SQL injection, and I managed to exploit it using UNION ALL in the first query to retrieve a user with a custom password like this ' UNION SELECT TOP 1 UserID, Username, 'MyCustomPassword' AS Passwort, ... FROM Users --.. However, the second query fails due to a column count mismatch (10 vs. 40 columns), throwing an error when UNION ALL is executed. The result of the second query is not important but the query should not throw. Is there any sql string for userid that would satisfy both queries syntax? Is this code really "safe" I also tried to insert a user into the table by concatenating a SELECT with an INSERT statement, but since CommitTransaction is never called, nothing actually happens. So while this code is indeed vulnerable to SQL injection, the transaction wrapping prevents any serious impact. This feels somewhat absurd—am I missing something?" EDIT: The userId is coming from the client and the transaction is rollbacked in the catch block therefore my initial approach with insert did not seem to work since the outer transaction is rollbacked when the second query is run (throws error) and therefore the transaction within the injected sql, or am I missing something?

A guy I know at college is claiming he can change his grades by gaining access to the database through an SQL inject, and can also gain access to all admin account privileges and records. The portal is accessed through outlook account and he did a pentest to prove it is vulnerable, ive attached the results. I am new to coding so not sure whether to believe it, is it really possible? from what I believed, the only systems vulnerable to this would be the worst ones, configured by somebody who has never used the database software before, which is extremely unlikely for this college. Are the pentest results real and should be concerned? Is it worth reporting to warn the college?

I'm on MySQL 5.5.21, and trying to insert the '\xF0\x9F\x98\x8A' smiley face character. But for the life of me, I can't figure out how to do it. According to various forums which I've been reading, it is possible. But whenever I try it, the data just gets truncated. mysql> INSERT INTO hour ( title, content, guid , published , lang , type , indegree , lon , lat , state , country , hour ) VALUES ( "title" , "content 😊 content" , "guid" , 1, 1, "WEBLOG", 1, 1, 1, "state" , "country" , 1 ); Query OK, 1 row affected, 2 warnings (0.00 sec) mysql> show warnings; +---------+------+-------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------------------------------+ | Warning | 1366 | Incorrect string value: '\xF0\x9F\x98\x8A ...' for column 'content' at row 1 | | Warning | 1265 | Data truncated for column 'published' at row 1 | +---------+------+-------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> select LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 687302 | +------------------+ 1 row in set (0.00 sec) mysql> select * from hour where id = 687302; +--------+-------+----------+------+---------------------+ | id | title | content | guid | published | +--------+-------+----------+------+---------------------+ | 687302 | title | content | guid | 0000-00-00 00:00:00 | +--------+-------+----------+------+---------------------+ 1 row in set (0.00 sec) But my table definition is as follows. CREATE TABLE hour ( id bigint(20) NOT NULL AUTO_INCREMENT, title varchar(255) CHARACTER SET utf8 NOT NULL, content text CHARACTER SET utf8 NOT NULL, guid varchar(255) CHARACTER SET utf8 NOT NULL, published timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, lang tinyint(3) unsigned NOT NULL, type enum('WEBLOG','MICROBLOG') CHARACTER SET utf8 DEFAULT NULL, indegree int(4) unsigned NOT NULL, lon float DEFAULT NULL, lat float DEFAULT NULL, state varchar(50) CHARACTER SET utf8 DEFAULT '', country varchar(50) CHARACTER SET utf8 DEFAULT '', hour int(2) DEFAULT NULL, gender enum('MALE','FEMALE') CHARACTER SET utf8 DEFAULT NULL, time_zone varchar(45) CHARACTER SET utf8 DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=MEMORY AUTO_INCREMENT=687560 DEFAULT CHARSET=utf8mb4 KEY_BLOCK_SIZE=288 One can see that I'm using CHARSET=utf8mb4. Surely this corrects issues around the use of multi-byte characters? Ok, so I didn't notice: content text CHARACTER SET utf8 NOT NULL, I've corrected that now, but still get funky results. CREATE TABLE hourtmp ( id bigint(20) NOT NULL AUTO_INCREMENT, title varchar(255) CHARACTER SET utf8 NOT NULL, content text NOT NULL, guid varchar(255) CHARACTER SET utf8 NOT NULL, published timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, lang tinyint(3) unsigned NOT NULL, type enum('WEBLOG','MICROBLOG') CHARACTER SET utf8 DEFAULT NULL, indegree int(4) unsigned NOT NULL, lon float DEFAULT NULL, lat float DEFAULT NULL, state varchar(50) CHARACTER SET utf8 DEFAULT '', country varchar(50) CHARACTER SET utf8 DEFAULT '', hour int(2) DEFAULT NULL, gender enum('MALE','FEMALE') CHARACTER SET utf8 DEFAULT NULL, time_zone varchar(45) CHARACTER SET utf8 DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=MEMORY AUTO_INCREMENT=687563 DEFAULT CHARSET=utf8mb4 KEY_BLOCK_SIZE=288 | mysql> INSERT INTO hourtmp ( title, content, guid , published , lang , type , indegree , lon , lat , state , country , hour ) VALUES ( "title" , "content 😊 content" , "guid" , 1, 1, "WEBLOG", 1, 1, 1, "state" , "country" , 1 ); Query OK, 1 row affected, 2 warnings (0.00 sec) mysql> show warnings; | Level | Code | Message | | Warning | 1366 | Incorrect string value: '\xF0\x9F\x98\x8A ...' for column 'content' at row 1 | | Warning | 1265 | Data truncated for column 'published' at row 1 | 2 rows in set (0.00 sec) mysql> select * from hourtmp; +--------+-------+-----------------------+ | id | title | content | +--------+-------+-----------------------+ | 687560 | title | content ???? content | | 687561 | title | content ???? content | +--------+-------+-----------------------+

I made the following stored procedure: ALTER PROCEDURE usp_actorBirthdays (@nameString nvarchar(100), @actorgender nvarchar(100)) AS SELECT ActorDOB, ActorName FROM tblActor WHERE ActorName LIKE '%' + @nameString + '%' AND ActorGender = @actorgender Now, I tried doing something like this. Maybe I am doing this wrong, but I want to be sure that such a procedure can prevent any SQL Injection: EXEC usp_actorBirthdays 'Tom', 'Male; DROP TABLE tblActor' The image below shows the SQL above being executed in SSMS and results being displayed correctly instead of an error: Btw, I added that part following the semicolon after the query was done executing. Then I executed it again, but when I checked to see if the table tblActor exists or not, it was still there. Am I doing something wrong? Or is this really injection-proof? I guess what I am trying to ask here also is that is a stored procedure like this safe? Thank you.

The following is a dynamic filtering solution that uses **sp_executesql** IF OBJECT_ID(N'dbo.GetOrders', N'P') IS NOT NULL DROP PROC dbo.GetOrders; GO CREATE PROC dbo.GetOrders @orderid AS INT = NULL, @custid AS INT = NULL, @empid AS INT = NULL, @orderdate AS DATE = NULL AS DECLARE @sql AS NVARCHAR(1000); SET @sql = N'SELECT orderid, custid, empid, orderdate, filler' + N' /* 27702431-107C-478C-8157-6DFCECC148DD */' + N' FROM dbo.Orders' + N' WHERE 1 = 1' + CASE WHEN @orderid IS NOT NULL THEN N' AND orderid = @oid' ELSE N'' END + CASE WHEN @custid IS NOT NULL THEN N' AND custid = @cid' ELSE N'' END + CASE WHEN @empid IS NOT NULL THEN N' AND empid = @eid' ELSE N'' END + CASE WHEN @orderdate IS NOT NULL THEN N' AND orderdate = @dt' ELSE N'' END; EXEC sp_executesql @stmt = @sql, @params = N'@oid AS INT, @cid AS INT, @eid AS INT, @dt AS DATE', @oid = @orderid, @cid = @custid, @eid = @empid, @dt = @orderdate; GO On p 541 of T-SQL Querying , it says > Because the dynamic code uses parameters rather than injecting the > constants into the code, it is not exposed to SQL injection attacks. How does the use of parameters in sp_executesql protect against SQL injection? Thank you

There is a table name queries in my dummy database which has name, email, phone, message, and region. And there is a contact form in the UI which directly inserts the data into the queries table without validating or sanitizing anything. So I was performing some SQL injection on the form by adding a','b','c','d','e'); DELETE FROM users; -- to one of the fields but it is not successful and getting the below error:- **Error:-**

Error: INSERT INTO queries (name,email,phone,message,region) VALUES('a','b','c','d','e'); DELETE FROM users; --','sdas','1234567890','fsfdsf','home')
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DELETE FROM users; --','sdas','1234567890','fsfdsf','home')' at line 1

**UI - Contact form** The SQL query in the error message seems okay to me and I couldn't understand why it is giving the syntax error. Maybe because the query is followed by a comment? Could someone please have a look and help me if I am missing anything? Thank you.

In Postgres, are prepared queries and user defined functions equivalent as a **mechanism for guarding against SQL injection**? Are there particular advantages in one approach over the other?

Most all answers and examples of SQL injection are showing some form of dynamic SQL or interpreting parameters as SQL. I haven't been able to find an example of the "correct" way. Microsoft and Oracle's documentation just shows examples of what not to do. So, I figured I should ask if this example of a stored procedure was protected against SQL injection attacks.

CREATE PROCEDURE test
    @username = varchar(30)
    @password = varchar(30)
AS
BEGIN
    SELECT *
    FROM credentials
    WHERE username = @username
    AND password = @password;
END
GO

Would this particular procedure be susceptible to SQL injections? I created the procedure and executed it with various attempts to inject SQL, such as EXEC test @password = '0; drop table credentials;', but was unable to do so. I figure I might not be doing the attack correctly.

Just for learning purposes, I'm trying to create a function using PLPGSQL and make an SQL injection on it. I recently learned about format, USING and quote_literal and quote_indent, so I'm good about avoiding an SQL injection. What I'm trying to do is create a function that allows an SQL injection (i.e. a drop table). So I wrote this:

create or replace function badfunc(tablename text, identifier int4)
returns setof character varying as $$
declare
    query text;
begin
    query := 'select full_name from ' || $1 || ' where re = ' || $2 ||'';
    raise notice 'query: %', query;
    return query execute query;
end;
$$ language 'plpgsql';

But when I execute this function with select badfunc('; drop table tb_students;', 1001); I get this error:

ERROR: syntax error at or near ";" where: function PL/pgSQL badfunc(text,integer) linha 7 in RETURN QUERY

So I think that's not how it is done. How can I achieve this SQL injection?

I'm trying to find a way to allow an application to create tables and insert data into them on a SQL Server 2019 while protecting from injection attacks in case the app credentials would leak. My experience is limited when it comes to writing code that can run in parallel and writing dynamic sql that is protected from sql injection attacks. The table name is based on input from the application, i.e. if the input is 'nds' the table name should be lake.nds_raw_log. It is my understanding that there is no way to do this via directly granting permissions to the role for this application since creating tables is not separated from deleting or altering them. What I've come up with is executing a stored procedure as dbo. Sure it's not long but I have two issues with it: - it feels contrived which by my experience says that there is an easier way. - I believe that I need to run it as serializable to avoid orphan tables if I retrieve the wrong table when I query for my newly created table. This shouldn't actually be that big of an issue since it won't happen that often after the first start in production so maybe I shouldn't care about it.

create procedure [lake].[create_terminal_raw_log_table]
    (
        @terminal_name nvarchar(100)
    )
    with execute as 'dbo'
    as
    	begin try
    		set transaction isolation level serializable
    		begin transaction
    			--create table
    			declare @dynamic_sql nvarchar(1000) =
    				'create table [lake].' + quotename(@terminal_name) + '
    				(
    					id bigint not null,
    					[timestamp] datetime2(3) not null,
    					cmd varbinary(max) not null
    				);'
    			exec sp_executesql @dynamic_sql
    
    			/*get name of new table, this is why I believe that I need serializable isolation
                since other tables can be created in parallel*/
    			declare @table_name nvarchar(100) =
    			(
    				select top 1
    					[name] as table_name
    				from sys.tables
    				order by create_date desc
    			)
    	
    			--rename table
    			declare
    				@old_name nvarchar(100) = '[lake].' + @table_name,
    				@new_name nvarchar(100) = @table_name + '_raw_log'
    			begin try
    				exec sp_rename
    					@objname = @old_name,
    					@newname = @new_name
    			end try
    			begin catch
    				set @dynamic_sql = 'drop table ' + @old_name
    				exec sp_executesql @dynamic_sql
    				;throw
    			end catch
    
    			--create primary key
    			set @dynamic_sql = 'alter table [lake].' + @new_name + ' add constraint pk__' + @new_name + ' primary key(id)'
    			exec sp_executesql @dynamic_sql
    
    		commit transaction
    	end try
    	begin catch
    		rollback --I thought a rollback would occur when I throw after dropping the table but that doesn't seem to be the case
    		;throw
    	end catch

So I guess this boils down to 3 questions: - Is this stored procedure actually safe from SQL injection attacks? - Is there an easier way to do it? - Is it correct that setting the transaction level as serializable will protect the code from selecting the wrong table when selecting from sys.tables?

Read it carefully, we have this query which is inserting values in the table called users. For the value member_id we are running a subquery to select from the table admin_users the id of the member. The reason why there are single quotes with +, it's because we are trying to manipulate the query. At this moment this first subquery works correctly but what happends with the second subquery? The second subquery selects the pass from the table settings, the table settings and the value pass totally exists and there is only one record, but this second query inside the INSERT INTO is not returning nothing. When the execution of the query INSERT INTO finishs, all the values are stored correctly except notes column which finally inserts 0. I don't know why but if you delete all the ''+ it works correctly the whole sql statement but in this time we can not delete ''+ because we are altering the query. I need a solution for this issue. INSERT INTO users (username,password,number,member_id,exp_date,notes) VALUES ('balvin','sjeneoeoe','3', ''+(select id from admin_users where username = 'TEST')+'', '1644622354', '' + (select pass from settings));#;'); Also i have tried modifying the second subquery like this but it didn't work. '' + (select pass from settings LIMIT 1) '' + (select pass from settings GROUP BY pass LIMIT 1) '' + (select pass from settings where id = 1 LIMIT 1) Perhaps the error it's the datatype of the column value pass in settings or the column notes in users CREATE TABLE users ( id int(11) NOT NULL AUTO_INCREMENT, member_id int(11) DEFAULT NULL, username varchar(255) COLLATE utf8_unicode_ci NOT NULL, password varchar(255) COLLATE utf8_unicode_ci NOT NULL, exp_date int(11) DEFAULT NULL, notes mediumtext COLLATE utf8_unicode_ci NOT NULL, number int(11) NOT NULL DEFAULT '1', PRIMARY KEY (id), KEY member_id (member_id), KEY exp_date (exp_date), KEY username (username), KEY password (password), ) ENGINE=InnoDB AUTO_INCREMENT=1702894 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci CREATE TABLE settings ( id int(11) NOT NULL, name mediumtext COLLATE utf8_unicode_ci NOT NULL, pass mediumtext COLLATE utf8_unicode_ci NOT NULL, ... ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

What is the proper way to implement a wildcard search in PostgreSQL when using a parameter in a function that uses dynamic SQL? As a starting point, here is an example from Erwin Brandstetter answering a different question on Stackoverflow: https://stackoverflow.com/a/12047277/538962 CREATE OR REPLACE FUNCTION report_get_countries_new (starts_with text , ends_with text = NULL) RETURNS SETOF lookups.countries AS $func$ DECLARE sql text := 'SELECT * FROM lookups.countries WHERE country_name >= $1'; BEGIN IF ends_with IS NOT NULL THEN sql := sql || ' AND country_name <= $2'; END IF; RETURN QUERY EXECUTE sql USING starts_with, ends_with; END $func$ LANGUAGE plpgsql; Let's suppose for country_name you wanted to do a leading and trailing wildcard search. E.g., without using a parameter, AND country_name LIKE '%ic%'. What is the best way to implement the wildcard search be in this scenario with respect to negating SQL injection risk? I am currently using PostgreSQL 9.5.1.

The psql \i command is able to execute a given SQL script but I need a way to pass parameters to the script. Example: say you have this simple script

select * from :table LIMIT 1;

I've tried

my_db=> \i my-script.sql -v table="core.product"

but got this error

psql:my-script.sql:1: ERROR:  syntax error at or near ":"
LINE 1: select * from :table LIMIT 1;
                      ^
\i: extra argument "-v" ignored
\i: extra argument "table="core.product"" ignored

--- I know that running this on terminal will work, but I'm already inside psql.

psql -v table="core.product" -f my-script.sql

Actually this question is a assignment given to me and I was not able to find any answer by googling. Is there any builtin mechanism in mariadb to stop sql injections?

Is it true that stored procedures prevent SQL injection attacks against PostgreSQL databases? I did a little research and found out that SQL Server, Oracle and MySQL are not safe against SQL injection even if we only use stored procedures. However, this problem does not exist in PostgreSQL. Does the stored procedure implementation in PostgreSQL core prevent SQL injection attacks or is it something else? Or is PostgreSQL also susceptible to SQL injection even if we only use stored procedures? If so, please show me an example (e.g. book, site, paper, etc).

I am trying to write a procedure that has a location parameter, this parameter is later used in the queries inside the procedure. Also, I want to be able to limit the query results dynamically. I have never done anything similar, so I read the appropriate documentation and studied some examples. My code is the following: DROP PROCEDURE IF EXISTS lower10_avg_price ; DELIMITER $$ CREATE procedure lower10_avg_price (loc varchar(45)) BEGIN SET @a = (SELECT ceil(COUNT(*)/10) FROM UNION_SALES WHERE location = loc and current_Price >5000); SET @quer = concat( 'SELECT ROUND(avg(prices.price),2) FROM (SELECT current_Price as price FROM UNION_SALES WHERE location = ', loc,' and current_Price >5000 ORDER BY current_Price ASC LIMIT ?) as prices;'); PREPARE STMT FROM @quer; EXECUTE STMT USING @a; END $$ DELIMITER ; The error I get when I call the procedure is: CALL lower10_avg_price('Αρχαία Αγορά '); **Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Αγορά and current_Price >5000 ORDER BY current_Price ASC LIMIT ' at line 3** Can someone please help me understand what is causing this error? Thank you for any suggestions!