0

There is a code for creating function

 CREATE FUNCTION GetMaxNoteId(User_id bigint)
RETURNS bigint
as
$$
    select greatest(
        (select max(noteid) from spending where userid = User_id),
        (select max(noteid) from income where userid = User_id))
$$ LANGUAGE SQL;

i tried to use something like this

 var a = db.Database.ExecuteSqlRaw($"select GetMaxNoteId({ChatId}) as noteid");

but it doesn't work correctly enter image description here

may be have i to use directly SQL in Ado.net like

NpgsqlConnection conn = new NpgsqlConnection(db.Database.GetConnectionString());
NpgsqlCommand cmd = new NpgsqlCommand($"select GetMaxNoteId({ChatId}) as noteid", conn);
conn.Open();
long GG = (long)cmd.ExecuteScalar();
conn.Close();
conn.Dispose();

i also tried alternative query with using EF however it doesn't work too

There is a code in direct sql in PGAdmin

Pecnik
  • 1
  • 2
  • `it doesn't work correctly` doesn't explain anything. What you wrote though fully exposes you to SQL injection attacks and I suspect the compiler is already emitting a warning. Use `FromSqlInterpolated` if you want to pass parameters using string interpolation. Your current code is constructing a SQL string from raw input. What if `ChatId` contained `1); Drop table users; --` ? – Panagiotis Kanavos Jul 25 '22 at 09:43
  • it doesn't work correctly means that this return -1 but it have to return 2, also chatId cannot contained sql injection because this variable generated inside the code and user don't have a possibility to enter there anything P.S. I also tried use FromSqlInterpolated but it doen't change the result – Pecnik Jul 25 '22 at 09:47
  • `return -1 but it have to return 2` why? You didn't post any data. As for `cannot contained sql injection` wrong. In fact, unless that `ChatId` is an int or long, you're *already* experiencing the effects of SQL injection. Any floating point, string or GUID would be serialized as a localized string into that query. Is there any reason at all to keep using the unsafe method when you can easily use the *safe* one? – Panagiotis Kanavos Jul 25 '22 at 09:52
  • It's impossible to help with so little information. Have you tried executing that query directly at least? `SELECT MAX()` won't return -1 unless that's the actual result. If there's no matching record it would return `NULL`. `GREATEST` would return `NULL` if all fields were NULL. – Panagiotis Kanavos Jul 25 '22 at 09:56
  • Okay why it returns -1 instead of 2 i dont know, there are 2 table which contains some data and noteid i specifically filled it just 2 record. There is just simple logics every user have unique noteId and 2 table this part of code have to take MAX noteid of these 2 tables, ChatId is Chatid in telegram chat and that is given from update.message.chat.id so if it return error whole app will be broken, but i will use parameter instead of interpolation thx. – Pecnik Jul 25 '22 at 09:59
  • Did you try executing that query directly against the database using a client tool? What did you get? Why assume that `-1` is wrong? The only guess that fits the data is that there's a `-1` stored in those tables. There may be some weird logic that uses `-1` as a default value. Post queries and sample data instead of describing them. If possible use https://www.db-fiddle.com/ , https://dbfiddle.uk/ or a similar site to create an example others can test. – Panagiotis Kanavos Jul 25 '22 at 10:06
  • I add screenshot in theme further in order to you can see that it returns -1 when it is impossible and yes i tried execute it directly in pgAdmin 4 and there it works correct, i add it in topic too – Pecnik Jul 25 '22 at 10:14
  • `ExecuteSqlRaw` returns the number of rows affected, if not relevant it returns -1. You need `FromSqlRaw` or `FromSqlInterpolated` – Charlieface Jul 25 '22 at 10:21
  • Does this answer your question? [ExecuteNonQuery doesn't return results](https://stackoverflow.com/questions/5349114/executenonquery-doesnt-return-results) – Charlieface Jul 25 '22 at 10:22
  • If i will use FromSqlRaw i have to choose from which table i want to choose it, but i don't choose from any tables, so i try to use direct sql in EF but i think im stupid or it is impossible to use SELECT state in ef directly. P.S. i've attached ado.net code because it works correctly but i don't want combinate ef and ado when it is required just 1 time – Pecnik Jul 25 '22 at 10:25

0 Answers0