How to read the contents of the SQL script that is uploaded and save the contents as text into DB

[steel wave]

I want to read the contents of the SQL script that is uploaded through a Submission form in Django and the content of the file which is a SQL query needs to be SQL linted and encoded in Base64 before saving it as a text in the DB, but unfortunately I'm getting the " raw_line in raw_str.splitlines():
AttributeError: 'SQLQuery' object has no attribute 'splitlines' " error.

Below is the code I wrote to perform the task.

views.py

@login_required(login_url='../login/')
def dashboard_view(request):
if request.method == 'POST':
form = DashboardSqlUploadForm(request.POST, request.FILES)
if form.is_valid():

       uploaded_file = request.FILES['upload_sql_script_file']
       str_text = ''
       for line in uploaded_file:
            str_text += line.decode()

       content = str_text

       print(content)

       sql_query = sqlvalidator.parse(content)
       if not sql_query.is_valid():
            print(sql_query.errors)
            messages.error(request, f'Error uploading your SQL script file. Please check your SQL script file and try again')
            return redirect('../dashboard')
       else: 
            #escaped_sql_query = re.escape(sql_query)
            sql_query_linted = sqlfluff.fix(sql_query, dialect="mariadb")
            sql_query_linted_str_bytes = sql_query_linted.encode('ascii')
            base64_bytes = base64.b64encode(sql_query_linted_str_bytes)
            sql_query_base64_text = base64_bytes.decode('ascii')
            sql_query_base64_text.save()
            messages.success(request, f'Your SQL script has been uploaded')
            return redirect('../dashboard')

form = DashboardSqlUploadForm()
return render(request, 'members/dashboard.html', {'form': form})

[covert tangle]

Please format your code according to the guidance in [Sharing Code in Discord](#1306622915365703681 message ) ... in short use backticks around your code
```python
code
```

[clever sand]

I don't know what sqlvalidator is, or what the actual traceback of your error is (please include it), but the error is pretty clear: you have a SQLQuery object called raw_str that you're trying to treat as a string. You can't—it's not a string. It's an SQLQuery object

[main lava]

My guess is that the problem is with passing the output of sqlvalidator.parse() to sqlfluff.fix(). It would be helpful if you share the whole error traceback

[steel wave]

sqlvalidator is used to check if the content which I assume is a string is SQL query.

[main lava]

sqlfluff.fix() expects a string.

[clever sand]

And sql_query is not a string

It is an SQLQuery object

I presume there's a way to get the raw string from it—or just use content, which is the raw SQL query

Or use sql_query.sql: https://github.com/David-Wobrock/sqlvalidator/blob/main/sqlvalidator/sql_validator.py#L9

[steel wave]

I'm a newbie in Django so I will ask some questions. When reading the contents of the uploaded file and saving them into content, content is not a string

?

[clever sand]

content is a string

[hexed pollen]

But the output of sqlivalidator.parse that you save to sql_query is not a string.

Nothing in your error is Django spesific, the error is that you are using the third-party sqlivalidator functions wrong.

[steel wave]

I understand. The idea was to use sqlivalidator.parse to check if content as a string is a SQL query and since the output of sqlvalidator.parse() is a SQLQuery object, is it possible to convert sql_query to a string or should I just pass as a parameter content since sqlfluff.fix() expects a string?

[main lava]

See what Ben shared above ⬆️

Is there a particular reason you're using sqlvalidator instead of sqlfluff to check if the sql is valid?

[steel wave]

sqlfluff is used to perform SQL Linting against the SQL string

[main lava]

I think it would be simpler to just use one library instead of two. You also run the risk of sqlvalidator thinking some statement is valid while sqlfluff does not.

[steel wave]

I went through the link shared by Ben and it doesn't specify how convert sql_query which is a SQLQuery object into a string. Maybe I should use the sqlvalidator to check if it is a query and then continue the process with sqlfluff to perform SQL linting against the content string like this:
def dashboard_view(request):
if request.method == 'POST':
form = DashboardSqlUploadForm(request.POST, request.FILES)
if form.is_valid():

       uploaded_file = request.FILES['upload_sql_script_file']
       str_text = ''
       for line in uploaded_file:
            str_text += line.decode()

       content = str_text

       print(content)

       sql_query = sqlvalidator.parse(content)
       if not sql_query.is_valid():
            print(sql_query.errors)
            messages.error(request, f'Error uploading your SQL script file. Please check your SQL script file and try again')
            return redirect('../dashboard')
       else: 
            #escaped_sql_query = re.escape(sql_query)
            sql_query_linted = sqlfluff.fix(content, dialect="mariadb")
            sql_query_linted_str_bytes = sql_query_linted.encode('ascii')
            base64_bytes = base64.b64encode(sql_query_linted_str_bytes)
            sql_query_base64_text = base64_bytes.decode('ascii')
            sql_query_base64_text.save()
            messages.success(request, f'Your SQL script has been uploaded')
            return redirect('../dashboard')

Is it the right approach?

[main lava]

Do you still get an error?

[covert tangle]

Please format your code according to the guidance in [Sharing Code in Discord](#1306622915365703681 message ) ... in short use backticks around your code
```python
code
```

[steel wave]

I got the following error when I wanted to save the encoded string as a text to the database:
File "/home/christian/tt_ops_self_service_web_portal/members/views.py", line 66, in dashboard_view
sql_query_base64_text.save()
AttributeError: 'str' object has no attribute 'save'

[clever sand]

That's a string—what are you trying to "save" it to?

I don't see you using any database models

[steel wave]

This is the model I created for the form:

class DashboardSQLUpload(models.Model):
project_name = models.CharField(max_length=100)
notes = models.TextField(null=True, blank=True, max_length=500)
ENVIRONMENT_CHOICES = [
('QA','QA Env'),
('PROD', 'PROD Env'),
]
environment = models.CharField(max_length=4, default='QA', choices=ENVIRONMENT_CHOICES, blank=True, null=True)
upload_sql_script_file = models.FileField(upload_to='script_files/',blank=True, null=True, validators=[FileExtensionValidator(allowed_extensions=['sql'])])

def __str__(self):
    return (f"{self.project_name} - {self.environment}")

[hexed pollen]

@steel wave Have you completed the official Django tutorial?

[steel wave]

I completed my tutorial using this resource: https://www.geeksforgeeks.org/django-tutorial/

[covert tangle]

Please format your code according to the guidance in [Sharing Code in Discord](#1306622915365703681 message ) ... in short use backticks around your code
```python
code
```

[steel wave]

After encoding the string as base64, the idea was to save its contents to the database

[clever sand]

So you need to save it in a model

You can't just call .save() on a string

You need to create an instance of whatever your database table model is, and save that

[steel wave]

Okay. In my case I have a model called DashboardSQLUpload which will create DashboardSQLUpload table in the database.

class DashboardSQLUpload(models.Model):
    project_name = models.CharField(max_length=100)
    notes = models.TextField(null=True, blank=True, max_length=500)
    ENVIRONMENT_CHOICES = [
            ('QA','QA Env'),
            ('PROD', 'PROD Env'),
    ]
    environment = models.CharField(max_length=4, default='QA', choices=ENVIRONMENT_CHOICES, blank=True, null=True)
    upload_sql_script_file = models.FileField(upload_to='script_files/',blank=True, null=True, validators=[FileExtensionValidator(allowed_extensions=['sql'])])

    def __str__(self):
        return (f"{self.project_name} - {self.environment}")

and the DashboardSQLUploadForm which will generate a form:

from django import forms
from .models import DashboardSQLUpload

class DashboardSqlUploadForm(forms.ModelForm):
    class Meta:
        model = DashboardSQLUpload
        fields = ['project_name', 'notes', 'environment', 'upload_sql_script_file']
        widgets = { 
            'project_name': forms.TextInput(attrs={'placeholder':'Project Name','class': 'form-control'}),
            'notes': forms.Textarea(attrs={'placeholder':'Notes',"rows":"3",'class': 'form-control'}),
            'environment': forms.Select(attrs={'placeholder':'Environment','class': 'form-control'}),
            'upload_sql_script_file': forms.FileInput(attrs={'accept':'.sql','placeholder':'SQL Script File','class': 'form-control'}),
        }

If I want to save the content of the encoding string into the DB, I would have to save it into the DashboardSQLUpload model or should I create another model where to save the content of that string?

[covert tangle]

Please format your code according to the guidance in [Sharing Code in Discord](#1306622915365703681 message ) ... in short use backticks around your code
```python
code
```

[clever sand]

Please try and listen to this 😅 ☝️

[steel wave]

Sorry Ben don't follow you?

[clever sand]

Every time you've pasted code directly into Discord, you've been asked to read the linked FAQ and format your code correctly within Discord so we can read it more easily. So far, you've ignored it each time.

[steel wave]

Oh okay. Sorry about that.

[clever sand]

You have an instance of DashboardSqlUploadForm in your view—if you call save on that, that will save the model to the database along with the uploaded SQL file.

[steel wave]

Okay, but instead of saving the uploaded SQL file, I want to save the SQL script that has been SQL Linted and encoded in base 64, do I need to have another model where this can be done?

Because I am following these requirements:
A user can submit a new request with the following information:
- Project
- Environment
- SQL script/query file
- Notes (Optional)
●​ Django submission page
(form) that does full validation
of script and submission detail.
●​ SQL injection projection as part of validation
●​ SQL linting as part of validation

This information should be persisted
The SQL query/script should be persisted in BASE64 encoding to protect the system
The SQL query/script input field needs to be protected against SQL injection

[clever sand]

If you don't want to save the uploaded file, why does your model have a FileField?

If you want to store the validated, linted, and base64-encoded SQL, shouldn't that be a TextField?

[steel wave]

I would go with a TextField. But the original idea is to upload the SQL script file, does all the validation that include SQL injection, linted and base64-encoded before saving it to the database as a text. Unless I can include a textfield and populate the contents of that file into the textfield before doing the submission?

[clever sand]

You can add a FileField to the form that's not on the model, and exclude the TextField from the form. Then in your view, you can read the file, populate the text field, and save the model then

[steel wave]

There is already a FileField in the form that is on the model for uploading the file. I should add the textField in the model and exclude it in the form, and I can add a TextField to the form that's not on the model, and exclude the TextField from the form. Then in your view, you can read the file, populate the text field, and save the model then, is that right?

[clever sand]

Yes

[steel wave]

Sorry, I made a mistake in my previous message I meant this: "There is already a FileField in the form that is based on the FileField in the model for uploading the file. I should add the textField in the model and exclude it in the form, then in my view, I can read the file, populate the text field, and save the model then, is that right?

[clever sand]

And remove the FileField from the model

[steel wave]

Okay. Remove the FileField from the model and leave it in the forms, Add the Textfield in the model but exclude it in the form, is that right?

[clever sand]

Yep

You want them to upload a file (that's the Form's responsibility) and save the text string (that's the Model's responsibility)

[steel wave]

Hi Ben. I wrote the models this way:

from django.db import models

class DashboardSQLUpload(models.Model):
    project_name = models.CharField(max_length=100)
    notes = models.TextField(null=True, blank=True, max_length=500)
    ENVIRONMENT_CHOICES = [
            ('QA','QA Env'),
            ('PROD', 'PROD Env'),
    ]
    environment = models.CharField(max_length=4, default='QA', choices=ENVIRONMENT_CHOICES, blank=True, null=True)
  
    sql_script_text_area = models.TextField(null=True, blank=True)

    def __str__(self):
        return (f"{self.project_name} - {self.environment}")

And the forms this way:

class DashboardSqlUploadForm(forms.ModelForm):
    class Meta:
        model = DashboardSQLUpload
        fields = ['project_name', 'notes', 'environment', 'upload_sql_script_file']
        exclude = ['sql_script_text_area']
        widgets = { 
            'project_name': forms.TextInput(attrs={'placeholder':'Project Name','class': 'form-control'}),
            'notes': forms.Textarea(attrs={'placeholder':'Notes',"rows":"3",'class': 'form-control'}),
            'environment': forms.Select(attrs={'placeholder':'Environment','class': 'form-control'}),
            'upload_sql_script_file': forms.FileInput(attrs={'accept':'.sql','placeholder':'SQL Script File','class': 'form-control'}),
        }

But I got the following error when I ran python manage.py runserver

django.core.exceptions.FieldError: Unknown field(s) (upload_sql_script_file) specified for DashboardSQLUpload

[clever sand]

Yeah, because it's not a field on the model

You need to remove it from fields and define it as a field under class DashboardSqlUploadForm(forms.ModelForm): (I don't know exactly how, I haven't done this for a while)

[steel wave]

Hi Ben. I think that is what you meant for the models.py and forms.py

from django.db import models

class DashboardSQLUpload(models.Model):
    project_name = models.CharField(max_length=100)
    notes = models.TextField(null=True, blank=True, max_length=500)
    ENVIRONMENT_CHOICES = [
            ('QA','QA Env'),
            ('PROD', 'PROD Env'),
    ]
    environment = models.CharField(max_length=4, default='QA', choices=ENVIRONMENT_CHOICES, blank=True, null=True)
  
    sql_script_text_area = models.TextField(null=True, blank=True)

    def __str__(self):
        return (f"{self.project_name} - {self.environment}")

from django.db import models

class DashboardSqlUploadForm(forms.ModelForm):
    upload_sql_script_file = forms.FileField(required=True, widget=forms.FileInput(attrs={'accept':'.sql','placeholder':'SQL Script File','class': 'form-control'}))
    class Meta:
        model = DashboardSQLUpload
        fields = ['project_name', 'notes', 'environment', 'upload_sql_script_file']
        exclude = ['sql_script_text_area']
        widgets = { 
            'project_name': forms.TextInput(attrs={'placeholder':'Project Name','class': 'form-control'}),
            'notes': forms.Textarea(attrs={'placeholder':'Notes',"rows":"3",'class': 'form-control'}),
            'environment': forms.Select(attrs={'placeholder':'Environment','class': 'form-control'}),
            'upload_sql_script_file': forms.FileInput(attrs={'accept':'.sql','placeholder':'SQL Script File','class': 'form-control'}),
        }

Is this the right way to save the file contents as a text in the database?

@login_required(login_url='../login/')
def dashboard_view(request):
    if request.method == 'POST':
        form = DashboardSqlUploadForm(request.POST, request.FILES)
        if form.is_valid():
           
           uploaded_file = request.FILES['upload_sql_script_file']
           str_text = ''
           for line in uploaded_file:
                str_text += line.decode()

           content = str_text

           print(content)

           sql_query = sqlvalidator.parse(content)
           if not sql_query.is_valid():
                print(sql_query.errors)
                messages.error(request, f'Error uploading your SQL script file. Please check your SQL script file and try again')
                return redirect('../dashboard')
           else: 
                #escaped_sql_query = re.escape(sql_query)
                sql_query_linted = sqlfluff.fix(content, dialect="mariadb")
                sql_query_linted_str_bytes = sql_query_linted.encode('ascii')
                base64_bytes = base64.b64encode(sql_query_linted_str_bytes)
                sql_query_base64_text = base64_bytes.decode('ascii')
                dashboard = DashboardSQLUpload(project_name=form.cleaned_data['project_name'], notes=form.cleaned_data['notes'], environment=form.cleaned_data['environment'], sql_script_text_area=sql_query_base64_text)
                dashboard.sql_script_text_area = sql_query_base64_text
                dashboard.save()
              
                messages.success(request, f'Your SQL script has been uploaded')
                return redirect('../dashboard')
    
    form = DashboardSqlUploadForm()
    return render(request, 'members/dashboard.html', {'form': form})

If I want to perform a SQL injection protection against a sql query string in Django, should I do this way:
escaped_sql_query = re.escape(content)

?

[clever sand]

Your DashboardSqlUploadForm already knows how to create an instance of a DashboardSQLUpload, you don't need to construct one manually. You should be able to do this: ```python
dashboard = form.save(commit=False)
dashboard.sql_script_text_area = sql_query_base64_text
dashboard.save()

[steel wave]

Cool cool thank you!

Is it the right way to implement SQL injection protection in a string by using escape characters
escaped_sql_query = re.escape(content)

[clever sand]

No idea I'm afraid. I always let the database/framework handle such things.