0

I have a SQL query where I have to pass a string in my where, my string can have a simple quote in the name of the program and at the same time break the string and create an error in my request.

Yes I would just like to skip the code, but the actual logic has been done so that we are able to modify the code, so I can't just trust that.

Here is the query in my ASP.NET MVC 5 project:

IQueryable<ListeProgrammesCol> query = db.Database.SqlQuery<ListeProgrammesCol>(
                    "SELECT id AS offreID, nomProgramme AS nom, codeProgramme AS code, dateAjout, dateLastUpdate, gestionEnLigne " +
                    "FROM tbl_offreCol " +
                    "WHERE FK_etablissement = " + instId +" AND offreType = 3 AND archive = 0 AND codeProgramme = '" + code + "' AND nomProgramme = '" + progNom + "' " +
                    "ORDER BY nomProgramme").AsQueryable();

And here is the query if you want to text in SQL Server Management Studio:

SELECT 
    id AS offreID, nomProgramme AS nom, codeProgramme AS code, 
    dateAjout, dateLastUpdate, gestionEnLigne 
FROM 
    tbl_offreCol 
WHERE 
    FK_etablissement = 923000 
    AND offreType = 3 
    AND archive = 0 
    AND codeProgramme = '351.A0' 
    AND nomProgramme = 'RAC en Techniques d'éducation spécialisée'
ORDER BY 
    nomProgramme

This is the problem: d'éducation

//////UPDATE

I decided to use linq to make my request, so I no longer need to use quotes. Here is the query:

var query = (from oc in db.tbl_offreCol
             where oc.FK_etablissement == instId 
             && oc.offreType == 3 
             && oc.archive == 0 
             && oc.codeProgramme == code 
             && oc.nomProgramme == progNom
             select new ListeProgrammesCol
             {
                 offreID = oc.id,
                 nom = oc.nomProgramme,
                 code = oc.codeProgramme,
                 dateAjout = oc.dateAjout,
                 dateLastUpdate = oc.dateLastUpdate,
                 gestionEnLigne = oc.gestionEnLigne
             }).OrderBy(x => x.nom).AsQueryable();
FrankLar21
  • 23
  • 6
  • ESCAPING is what you are looking for. Which is an issue as old as programming. In most RDBMS (Oracle/MySQL/SQL Server...), you can simply escape single quotes by *doubling* them. Its up to your code in the MVC to take that in charge prior to launch the SQL query. But you haven't specified neither the RDBMS you use, nor the programming language so its hard to answer precisely. – Thomas G May 10 '21 at 19:07
  • Does this answer your question? [How to use DbContext.Database.SqlQuery(sql, params) with stored procedure? EF Code First CTP5](https://stackoverflow.com/questions/4873607/how-to-use-dbcontext-database-sqlquerytelementsql-params-with-stored-proced) – Igor May 10 '21 at 19:46
  • 1
    **Always use parameterized sql and avoid string concatenation** to add values to sql statements. This mitigates SQL Injection vulnerabilities and ensures values are passed to the statement correctly. See [How can I add user-supplied input to an SQL statement?](https://stackoverflow.com/q/35163361/1260204), and [Exploits of a Mom](https://xkcd.com/327/). – Igor May 10 '21 at 19:47
  • Another duplicate answer https://stackoverflow.com/a/25497384/1260204 – Igor May 10 '21 at 19:47
  • After reading your comments, I turned on that I could make my query in linq. I updated my post with the request. Thank you for your answers, I will be able to apply it in other requests – FrankLar21 May 11 '21 at 11:47

0 Answers0