-1

I am trying to replace the first word that is in quotation marks from a string a to the first instance of ' value ' in string b and so on..

a = What is the id where we have 'sony electronics' as company name and 'pc' as the device type?

b = '''select * from DIM_DEVICE_TYPE where COMPANY_NAME = ' value ' and DEVICE_TYPE = ' value ' '''

expected output

select * from DIM_DEVICE_TYPE where COMPANY_NAME = 'sony electronics' and DEVICE_TYPE = 'pc'

Trapli
  • 1,517
  • 2
  • 13
  • 19
Abhishek Mamdapure
  • 69
  • 1
  • 3
  • 2
    For SQL, use substitution to prevent injection. https://stackoverflow.com/questions/40679501/how-to-substitute-variable-to-query-sql – Trapli Feb 28 '20 at 09:41
  • Show what you tried as properly formatted code in the question. – Michael Butscher Feb 28 '20 at 09:43
  • Does this answer your question? [How to substitute variable to query SQL?](https://stackoverflow.com/questions/40679501/how-to-substitute-variable-to-query-sql) – Błotosmętek Feb 28 '20 at 09:46
  • I have as far tried this, but unable to achieve what I want import re f = open(filename, 'r+') text = f.read() text = re.sub(" value ",aa[0], text) f.seek(0) f.write(text) f.truncate() f.close() – Abhishek Mamdapure Feb 28 '20 at 09:47

2 Answers2

0

You can extract the parameters with a regular expression '(.*?)'.*?'(.*?)'. Then you should use parameters substitution into your SQL request, don't use simple string replacement.

UPDATED

Why you don't want to use a simple string replacement to form an SQL request? Because you expose yourself to an SQL injection hacking attack. Consider that someone provides you the following input:

What is the id where we have '\' as company name and ' OR 1=1;' as the device type?

Now you have two matching groups of characters between apostrophes: 

group 1: a single backslash character
group 2: a string ' OR 1=1;'

After substitution this into the output string you'll get

select * from DIM_DEVICE_TYPE where COMPANY_NAME = '\' and DEVICE_TYPE = ' OR 1=1;'

What will happen next? For example MySQL treats a backslash in front of an apostrophe as an escape character, so it will treat this select as a search for a company with a very strange name ' and DEVICE_TYPE = (yes, the company name starts with an apostrophe), and this search is combined with a condition OR 1=1. Since 1=1 condition is always true the whole condition for search is true and you'll get the full list of all entries in you DIM_DEVICE_TYPE table.

Alex Sveshnikov
  • 4,214
  • 1
  • 10
  • 26
-1

you can use a regular expression to extract the wanted substrings from a and then you can use str.replace to replace within b

import re


a = "What is the id where we have 'sony electronics' as company name and 'pc' as the device type?"
b = "select * from DIM_DEVICE_TYPE where COMPANY_NAME = ' value ' and DEVICE_TYPE = ' value '"

it = re.finditer("'(.+?)'", a)

value = ' value '
for _ in range(b.count(value)):
    b = b.replace(value, next(it).group(1), 1)

b

outptu:

"select * from DIM_DEVICE_TYPE where COMPANY_NAME = 'sony electronics' and DEVICE_TYPE = 'pc'"

you may have a look over:

kederrac
  • 16,819
  • 6
  • 32
  • 55