I have a question similar to the thread talking about how to call a Parent form from a child form. I’d like to select individual rows using a check box. I’m envisioning triggering an event that labels a “Selected” column in my database as True when each individual check box is checked. Is this possible? Does each row have any distinguishable name I can call to get to one of the values in the row?
Are you using a repeating panel (or just line item forms) for each database row you are displaying? The rest of this assumes you are, but that assumption might be wrong. If you’re not you probably should be if you are displaying and manipulating tabular data. I’m also assuming you are using a PostgreSQL database (I seem to remember you were going to).
In your database you would normally have a field that contains some unique value for each row. Often this is an autoincremented integer that the DB adds on each INSERT (see here to add one : https://stackoverflow.com/questions/7718585/how-to-set-auto-increment-primary-key-in-postgresql)
Make sure you retrieve this value in your SELECT and store it in your line item form (you don’t even have to display it), then on the checkbox “change” event you would UPDATE the table row using this stored unique value in the WHERE clause, for example :
UPDATE mytable SET selected=true WHERE unique_id=<the id you stored>
Remember that each instance of the line item form is self contained, so it will have no knowledge of the others.
Does that help?
I am currently sticking to the repeating rows of the DataForm method I learned previously, rather than converting to a new RepeatingPanel. I assume this means I’m using the “line item forms” method still? So the trick is to write my code for this action in the repeating form I have? This looks like it should do the trick for me, I do have a unique primary key in this table.
Correct on all counts. The repeating panel is a baked in convenience version of the same thing.
If you get stuck, I’ve done this quite a few times and can probably help.
Chris, I’m doing the exact same thing, and it works fine (although I’m using Anvil’s DataTables). Regarding the unique row ID, I’m just appending it to the .tag property of one of the data elements I’m displaying on the form. You could also just stick it in a hidden label attached to each row.
Hi Chris,
Yes, this looks like you’re on the right track!
How are you initialising the form instance for each row? Are you passing a dictionary into the constructor and saving that? If so, if you add a unique ‘id’ column to the database and SELECT it, you can just use self.row['id'] (or similar) to uniquely identify which row just got its checkbox clicked. You can then pass this value to the server function, which can use it in its UPDATE statement.
I’m doing it as you described. I haven’t gotten a chance to fully implement this yet, I’ve had to deal with a couple other issues. But to faithfully stick to my theme of speed, which crops up in every question I ask, here’s a follow-up question about speed (…avoiding Top Gun references at all costs 
).
Question:
My app will likely have anywhere from 0-700 “select” instances. I obviously need a “select all” based on the search results. And I need a select-by-checkbox. But if the user gets click-happy on the checkboxes, I could see all those calls to the server slowing things down. So I might need an “Apply” button that implements a mass select on those rows with a checked box. A this would probably complicate things.
Can I handle this by creating a list, array or dictionary of the selected rows. And then the “Apply” button sets all the rows in the list/etc. to “selected”? If the list isn’t created on the Anvil server though, would that open me up to SQL injection?
If you feel the need (sorry, one of us had to) …
I would do as you say, have an apply button that steps through all the selected checkboxes (with a “are you sure?” box before performing the DB update). I don’t like people updating databases in “real time” without some form of confirmation, even on single selections.
I would make the apply button step through each control, so something like this on the “master” form (pseudo code) :
for p in self.repeating_panel_1.get_components():
if p is Type(Checkbox):
if p.checkbox.selected == True:
# store the ID in an array.
(edit - I know you’re not using repeating panel, so just step through your main form instead)
Then at the end send all the IDs in the array to the server where you can validate the request.
The “Select all” would do something similar setting the checked values.
If the user can make choices, then a malicious user can do so too. That’s not really SQL injection though in its commonly understood form (which is tricking the server into performing an unauthorised action through carefully scripted SQL parameters). All updates, even single clicks, must be validated so an en masse update is really no different other than in its scope.
I’m open to other ways to achieve the desired result than modifying the database. The reason I’m selecting rows is to export a report (csv or otherwise). Would it be better to just make a dictionary of id’s and do one sql query to export them all? Or is that the same thing?
Could I save the text of whichever row I select in a list without querying my DB at all?
if i is Type(Checkbox):
should be:
if type(i) is CheckBox:
P.S. Changed var p to i because of some weird formatting problem
1 Like
This isn’t working for me. self.column_panel.get_components() spits out “<DataDisplay.DataDisplay object>”. DataDisplay is my embedded form that displays the results of the SQL query in tabular form. I can’t find a way to get more detail or dig deeper to get into DataDisplay from the main form.
What is working for me is appending the id to .tag as PeterJ suggested as long as I do it in the DataDisplay form.
So back to my SQL injection question. Can someone simply change one of the .tag elements in their browser to “DROP table” and when I send the list with that malicious .tag element to my SQL DB it destroys my table?
Use what works for you, but I’ll try and dig out an example of what I do. Chances are your column panel is a collection of line item forms, so (again in untested pseudocode) you probably need to do something like :
for outer in self.column_panel.get_components():
for inner in outer.get_components():
if type(inner) is Checkbox:
# etc.
Re SQL injection - you must both sanitise the client data and ensure you use your database parameter escaping features to avoid this. This is compulsory reading :
http://initd.org/psycopg/docs/usage.html
especially this bit :
Warning
Never, never, NEVER use Python string concatenation (+) or string parameters interpolation (%) to pass variables to a SQL query string. Not even at gunpoint.
The correct way to pass variables in a SQL command is using the second argument of the execute() method:
>>> SQL = “INSERT INTO authors (name) VALUES (%s);” # Note: no quotes
>>> data = (“O’Reilly”, )
>>> cur.execute(SQL, data) # Note: no % operator
I’ve read and reread that section of the psycopg2 docs and have never been sure if I understand it. If understand it right, the trick is to always insert your “data” variable into the cur.execute() statement (as opposed to concatenating at the end of the ‘SQL’ variable) and always have the escape “,” as the last thing in the data variable. Can you clarify if that’s correct?
For example, is this bad?
>>> SQL = “INSERT INTO authors (name) VALUES (%s);”, ("O'Reilly",)
Or what about this?
>>> SQL = “INSERT INTO authors (name) VALUES (%s);”, ("O'Reilly") — note, not ending with “,”
Am I missing the point?
Yes, you are correct - split your sql and your parameters into two variables to avoid confusion, like this :
sql = "INSERT INTO mytable (name,address) VALUES(%s,%s)"
data=("David","Bath")
cur.execute(sql,data)
“data” is an array of values to be inserted. The execute function will take care of ensuring nothing malicious gets through in your data parameter.
The comma on the end of the data is, I believe, only required for a single parameter. It’s not an escape, it’s to force a single value into a tuple which I believe is the required type. I’ve never actually tried a single parameter so i can’t confirm that to be true.
Re your string examples, I’ve not tested them but I personally wouldn’t do it like that if only for the uncertainty. My method above should work fine and is much clearer in its intent.
Hope that helps (and anyone better than me jumps in with corrections!)