-1

Running a SQL query like shown below. Here if the name is "Tim's Store" how do I escape it in my query below.

Most of the time the name variable will not have a ' but sometimes it does and I run into an error.

How do I escape this?

name = Tim's store

var sql = `INSERT INTO CHAT VALUES ('${id}', '${name}')`

DBconnection.query(sql)
Dale K
  • 25,246
  • 15
  • 42
  • 71
Winterella
  • 35
  • 1
  • 5
  • 1
    I would strongly suggest you not try to escape the string value, but instead use placeholders in your SQL. What language are you using? – Andy Lester Jul 15 '22 at 19:15
  • 1
    no use prepared statements only – nbk Jul 15 '22 at 19:21
  • @AndyLester Javascript. Like use replace("'", "''"). will this work? – Winterella Jul 15 '22 at 19:24
  • 1
    Your code seems vulnerable to SQL Injection. Use parameterized statements instead. – The Impaler Jul 15 '22 at 19:27
  • @Winterella You'll notice that multiple people are telling you to use prepared statements with placeholders instead of trying escape your strings. The problem you are having, where the single quote in `Tim's store` is making your SQL fail, also means that you're open to SQL injection. You need to use SQL placeholders rather than building the SQL using variables. I don't know exactly how Javascript does it, but this article seems like a good place to start: https://www.veracode.com/blog/secure-development/how-prevent-sql-injection-nodejs – Andy Lester Jul 15 '22 at 19:30

1 Answers1

0

In order to escape your name variable, you can first place the full string in double quotes and then list the apostrophe as you currently have it as shown here:

name = "Tim's store";

You can find another example of this here: https://www.digitalocean.com/community/tutorials/how-to-work-with-strings-in-javascript#using-the-alternate-string-syntax

And if you're attempting to escape any non-alphanumeric symbols as a means to stop hacking, you would need to use question mark place holders as noted here (pseudocode):

name = "Tim's store";

var sql = `INSERT INTO CHAT VALUES ( ? , ?)`

DBconnection.query(sql)

However, I've noticed that the post is tagged as working with javascript and SQL but the function after your sql query is a .NET function.

But, I'll stick to what the tags on the post stated (ie javascript). In order to have javascript (a client-side/front-end code) work with SQL (server side), you would first need to use NodeJs in order to work with SQL statement and escape your.

You would need to first use the code found on this page, https://www.geeksforgeeks.org/node-js-mysql-insert-into-table/ , to learn how to connect to your database with nodejs and then how to add an insert statement that's escaped using the question mark symbols as shown in the code examples.

cstax
  • 47
  • 1
  • using double quotes isn't an option too. Some of the strings contain double quotes too so i will have to escape them as well. any other way? – Winterella Jul 15 '22 at 20:56
  • Yes, please check the example and result section here: https://www.afterhoursprogramming.com/tutorial/javascript/backslash-characters/ . You'll see that you can use a backslash to escape single quotes as well as other non-alphanumeric characters. – cstax Jul 15 '22 at 22:38
  • 1. the escaping example is misleading. The OP is talking about the contents of the variable, not about definig a string in the code. 2. Why linking some external resource instead of linking an answer from Stack Overflow? 3. Which leads us to the final question, why answering a question that has been answered a billion times already? – Your Common Sense Jul 16 '22 at 04:10